How to Calculate Cumulative Sum in PostgreSQL Easily

In PostgreSQL, you calculate a cumulative sum using the SUM() window function combined with OVER(). This lets you add values progressively across rows, often ordered by a column like date or id.

Syntax

The cumulative sum uses the SUM() function as a window function with OVER(). Inside OVER(), you specify the order of rows to sum progressively.

• SUM(column_name): adds values from the specified column.
• OVER (ORDER BY column_name): defines the order to calculate the running total.

sql

SELECT column_name, SUM(column_name) OVER (ORDER BY column_name) AS cumulative_sum FROM table_name;

Example

This example shows how to calculate a cumulative sum of sales amounts ordered by date.

sql

CREATE TABLE sales (
  sale_date DATE,
  amount INT
);

INSERT INTO sales VALUES
('2024-01-01', 100),
('2024-01-02', 150),
('2024-01-03', 200);

SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sum FROM sales ORDER BY sale_date;

Output

 sale_date | amount | cumulative_sum 
-----------+--------+----------------
 2024-01-01 |    100 |            100
 2024-01-02 |    150 |            250
 2024-01-03 |    200 |            450

Common Pitfalls

Common mistakes include:

• Not using ORDER BY inside OVER(), which causes the cumulative sum to be incorrect or unordered.
• Using GROUP BY instead of window functions, which aggregates rows instead of calculating running totals.
• Forgetting to order the final query, which can make results appear unordered.

sql

/* Wrong: No ORDER BY in OVER() */
SELECT amount, SUM(amount) OVER () AS cumulative_sum FROM sales;

/* Right: Include ORDER BY for correct running total */
SELECT amount, SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sum FROM sales;

Quick Reference

Feature	Description
SUM() OVER (ORDER BY column)	Calculates cumulative sum ordered by column
ORDER BY inside OVER()	Defines the order for running total calculation
No GROUP BY needed	Window functions work on rows without grouping
Use ORDER BY in final SELECT	Ensures output rows are in correct order

Key Takeaways

Use SUM() as a window function with OVER(ORDER BY column) to calculate cumulative sums.

Always specify ORDER BY inside OVER() to get correct running totals.

Avoid GROUP BY when you want a running total; use window functions instead.

Order your final query results to see cumulative sums in the right sequence.