Postgresql: Calculate monthly running total with some months missing

Question

I have a table like this:

| acct| month | total |
-------------------------------
| 123 | 02    | 100
| 123 | 03    | 100
| 123 | 04    | 100
| 123 | 06    | 100
| 123 | 07    | 100

I want to get a running total grouped by acct for each month. However as shown above the table does not have a record for month 5 (basically nothing changed in month 5), but I still want to create a row for month 5 that will be the same as the previous month 4 so that the result looks like:

| acct| month | total |
-------------------------------
| 123 | 02    | 100
| 123 | 03    | 200
| 123 | 04    | 300
| 123 | 05    | 300
| 123 | 06    | 400
| 123 | 07    | 500

Is there anyway to do this in Postgresql? I've explored using over and partition as described here Calculating Cumulative Sum in PostgreSQL but that is for the case where all months are present.

How does this add up to 600? There is only 500 in the original table. — Gordon Linoff
– Gordon Linoff, Commented Aug 17, 2018 at 22:38

Gordon Linoff · Accepted Answer · 2018-08-17 22:39:47Z

2

Assuming you really want a cumulative sum with missing months, use generate_series() to generate the dates and then left join and a cumulative sum:

select t.acct, gs.mon, sum(total) over (order by mon)
from generate_series(2, 7, 1) gs(mon) left join
     t
     on gs.mon = t.mon;

answered Aug 17, 2018 at 22:39

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql: Calculate monthly running total with some months missing

I have a table like this:

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

I have a table like this:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related