Calculating Cumulative Sum in PostgreSQL

Basically, you need a window function. That's a standard feature nowadays. In addition to genuine window functions, you can use any aggregate function as window function in Postgres by appending an OVER clause.

The special difficulty here is to get partitions and sort order right:

SELECT ea_month, id, amount, ea_year, circle_id     , sum(amount) OVER (PARTITION BY circle_id                         ORDER BY ea_year, ea_month) AS cum_amtFROM   tblORDER  BY circle_id, month;

And no GROUP BY.

The sum for each row is calculated from the first row in the partition to the current row - or quoting the manual to be precise:

The default framing option is RANGE UNBOUNDED PRECEDING, which isthe same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. WithORDER BY, this sets the frame to be all rows from the partitionstart up through the current row's last ORDER BY peer.

... which is the cumulative or running sum you are after. Bold emphasis mine.

Rows with the same (circle_id, ea_year, ea_month) are "peers" in this query. All of those show the same running sum with all peers added to the sum. But I assume your table is UNIQUE on (circle_id, ea_year, ea_month), then the sort order is deterministic and no row has peers.

Postgres 11 added tools to include / exclude peers with the new frame_exclusion options. See:

• Aggregating all values not in the same group

Now, ORDER BY ... ea_month won't work with strings for month names. Postgres would sort alphabetically according to the locale setting.

If you have actual date values stored in your table you can sort properly. If not, I suggest to replace ea_year and ea_month with a single column mon of type date in your table.

• Transform what you have with to_date():

    to_date(ea_year || ea_month , 'YYYYMonth') AS mon

• For display, you can get original strings with to_char():

    to_char(mon, 'Month') AS ea_month  to_char(mon, 'YYYY') AS ea_year

While stuck with the unfortunate design, this will work:

SELECT ea_month, id, amount, ea_year, circle_id     , sum(amount) OVER (PARTITION BY circle_id ORDER BY mon) AS cum_amtFROM   (SELECT *, to_date(ea_year || ea_month, 'YYYYMonth') AS mon FROM tbl)ORDER  BY circle_id, mon;