'How do I make an SQL query in Postgres that finds total balance and applies credit fees for the year?

I am totally new to Postgres and I cant find any example of what I am trying to do...

I have a table of transactions for a year:

amount | date

12     |  1980-02-12
-200   |  1980-03-06
30     |  1980-03-14

Positive transactions for incoming money.

Negative transactions for credit payments.

And I need to return a total for the year that also reflects charging a $20 fee for every month where less than $400 in credit was used, like so:

total
-------
80,401

My thought was that I would first find total credit for all months like this...

WITH month_credit_totals 
AS
(SELECT 
    SUM(amount) AS total_credit 
FROM transactions 
WHERE amount < 0
GROUP BY DATE_TRUNC('month', date))

and from there I would find the amount of months with less than $400 in credit payments like this...

SELECT
    COUNT(*)
FROM month_credit_totals
WHERE total_credit <= -400

I wanted to save off this number, subtract it from 12, multiply that result by -20, and get the total amount owed in credit fees for the year that way.

Then, I thought I could just total up the amount column, apply that total fee amount, and that would be my result.

But I am having so much trouble understanding the syntax as a complete beginner, I cant find a way to save off variables, do the math, and apply that to total. I am constantly getting syntax errors that are pointing me in no direction just when I try to declare a variable at all.

Maybe I'm going about this the completely wrong way?

I would appreciate any advice. Thanks!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source