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!
Advertisement
Answer
You’re on the right track, but you don’t really need to save these variables anywhere, just calculate it on the fly.
WITH credit_by_month AS
(
SELECT DATE_TRUNC('month', dt), SUM(amount) AS total_credit
FROM transactions
WHERE amount < 0
GROUP BY DATE_TRUNC('month', dt)
)
SELECT COUNT(*) * 20 AS credit_fee
FROM credit_by_month
WHERE total_credit > -400
Here’s a working demo on dbfiddle, it’s using random data so it will return a different number every time.