Calculating Cumulative Sum in PostgreSQL

by

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_amt
FROM   tbl
ORDER  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 is
the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With
ORDER BY, this sets the frame to be all rows from the partition
start 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:

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_amt
FROM   (SELECT *, to_date(ea_year || ea_month, 'YYYYMonth') AS mon FROM tbl)
ORDER  BY circle_id, mon;

More Related Contents:

  1. PostgreSQL unnest() with element number
  2. Dynamic alternative to pivot with CASE and GROUP BY
  3. PostgreSQL: running count of rows for a query ‘by minute’
  4. GROUP BY and aggregate sequential numeric values
  5. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  6. Get the distinct sum of a joined table column
  7. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  8. Window Functions or Common Table Expressions: count previous rows within range
  9. Postgres window function and group by exception
  10. Referencing current row in FILTER clause of window function
  11. How to group timestamps into islands (based on arbitrary gap)?
  12. How to use a ring data structure in window functions
  13. Conditional lead/lag function PostgreSQL?
  14. Select rows which are not present in other table
  15. Optimize GROUP BY query to retrieve latest row per user
  16. How to implement a many-to-many relationship in PostgreSQL?
  17. Check if value exists in Postgres array
  18. Is there a shortcut for SELECT * FROM?
  19. sql statement error: “column .. does not exist”
  20. Return multiple columns of the same row as JSON array of objects
  21. Getting all Buildings in range of 5 miles from specified coordinates
  22. Call a set-returning function with an array argument multiple times
  23. Fastest check if row exists in PostgreSQL
  24. Discard millisecond part from timestamp
  25. SQL column reference “id” is ambiguous
  26. IN Clause with NULL or IS NULL
  27. postgres – where in (list) – column does not exist
  28. incomplete information from query on pg_views [duplicate]
  29. SQL Server Cumulative Sum by Group
  30. How to use SQL LIKE condition with multiple values in PostgreSQL?

Leave a Comment