'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 |
|---|
