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, Aug 17 '18 at 22:38

score 2 · Answer 1 · answered Aug 17 '18 at 22:39

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;

Postgresql: Calculate monthly running total with some months missing

I have a table like this:

1 Answers1