How to Use Running Total in PostgreSQL: Syntax and Examples

In PostgreSQL, you can calculate a running total using the SUM() window function combined with OVER(). This lets you add up values cumulatively across rows in a specified order without grouping the results.

Syntax

The running total is calculated using the SUM() function as a window function. The basic syntax is:

• SUM(column_name) OVER (ORDER BY column_to_order): Calculates the cumulative sum of column_name ordered by column_to_order.
• OVER() defines the window (set of rows) for the calculation.
• ORDER BY inside OVER() specifies the order in which rows are processed.

sql

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

Example

This example shows how to calculate a running total of sales amounts ordered by date.

sql

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  sale_date DATE,
  amount NUMERIC
);

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

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

Output

 sale_date | amount | running_total 
-----------+--------+---------------
 2024-01-01 |    100 |           100
 2024-01-02 |    150 |           250
 2024-01-03 |    200 |           450
 2024-01-04 |     50 |           500
(4 rows)

Common Pitfalls

Common mistakes when using running totals include:

• Not specifying ORDER BY inside OVER(), which causes the running total to be incorrect or unordered.
• Using GROUP BY which aggregates rows and prevents running totals from showing cumulative sums per row.
• Forgetting to order the final SELECT results, which can make the running total appear inconsistent.

sql

/* Wrong: Missing ORDER BY in OVER() */
SELECT sale_date, amount,
       SUM(amount) OVER () AS running_total
FROM sales
ORDER BY sale_date;

/* Right: Include ORDER BY in OVER() */
SELECT sale_date, amount,
       SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

Quick Reference

Feature	Description
SUM() OVER (ORDER BY column)	Calculates running total ordered by column
ORDER BY in OVER()	Defines the order for cumulative calculation
No GROUP BY	Avoid grouping to keep row-level running totals
ORDER BY in SELECT	Sort final output for correct running total display

Key Takeaways

Use SUM() as a window function with OVER(ORDER BY ...) to calculate running totals.

Always specify ORDER BY inside OVER() to get correct cumulative sums.

Do not use GROUP BY when you want running totals per row.

Order your final SELECT results to display running totals properly.

Running totals help track cumulative values like sales or balances over time.