Creating month intervals to calculate total payment on a monthly basis starting from the first date through the last date of payment

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:

months total_amount
2020-01-13 2399.00
2020-02-13 1988.00
2020-03-13 3023.00

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

1 Like