I have a payments table in my own database and I have the following columns:
payment_id - INT
payment_date - TIMESTAMP
customer_id - VARCHAR
payment_amount - DOUBLE
I am trying to run a query to give me a table with total payment amount on a monthly basis starting from the first payment date such as the sample below:
The months column can also start from the first day of each month like such as 2020-01-01, 2020-02-01…
Thanks a lot!
So what have you tried so far, what’s the plan?
Hey tgrtim, thanks for reply. I actually managed to write the syntax that gives me the response, but not sure if this is the most practical way. This is in Postgresql and I am also calculating the running total.
with cte as (select generate_series(date_trunc(‘month’,min(payment_date)),date_trunc(‘month’,max(payment_date)),interval ‘1 month’)::date as months from payment)
select cte.months, sum(p.amount), sum(sum(p.amount)) over (order by cte.months) as running_total from payment as p
join cte on cte.months = date_trunc(‘month’,p.payment_date)::date
group by 1
this yields a table like this:
months sum running_total
2007-02-01. 8000 8000
2007-03-01 23000 31000