Running totals, moving averages and frames

Running totals

A running total (or cumulative sum) answers "how much have we sold so far this month?" on every day. The daily_sales table below has the revenue of two stores, Pune and Mumbai, for six days in March 2025. Add ORDER BY inside OVER, and SUM adds up all rows from the start up to the current one.

SELECT sale_date, revenue,
       SUM(revenue) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales
WHERE store = 'Pune'
ORDER BY sale_date;
-- 2025-03-01 | 12000 | 12000
-- 2025-03-02 | 15000 | 27000
-- 2025-03-03 | 9000  | 36000
-- 2025-03-04 | 18000 | 54000
-- 2025-03-05 | 14000 | 68000
-- 2025-03-06 | 21000 | 89000

Frames: which rows are in the window

Inside a partition, the frame is the set of rows the function actually looks at for the current row. You can write it after the ORDER BY. The words are: UNBOUNDED PRECEDING (the first row of the partition), n PRECEDING (n rows before), CURRENT ROW, n FOLLOWING and UNBOUNDED FOLLOWING (the last row).

The tie surprise: RANGE versus ROWS

The default frame uses RANGE, which means "all rows whose ORDER BY value is up to the current value". Rows with the same value are called peers, and RANGE always takes all peers together. ROWS counts physical rows instead. With unique dates you never notice. With ties you do.

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary)                          AS range_total,
       SUM(salary) OVER (ORDER BY salary ROWS UNBOUNDED PRECEDING) AS rows_total
FROM employees ORDER BY salary, name;
-- ...
-- Lakshmi Pillai | 80000 | 325000 | 325000
-- Anita Desai    | 95000 | 515000 | 420000
-- Vikram Singh   | 95000 | 515000 | 515000
-- Farhan Ali     | 105000| 620000 | 620000

Moving averages

A moving average smooths out daily ups and downs. A 3-day moving average is the average of today and the two days before. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives exactly that frame.

SELECT sale_date, revenue,
       ROUND(AVG(revenue) OVER (ORDER BY sale_date
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma_3day
FROM daily_sales
WHERE store = 'Pune'
ORDER BY sale_date;
-- 2025-03-01 | 12000 | 12000
-- 2025-03-02 | 15000 | 13500
-- 2025-03-03 | 9000  | 12000
-- 2025-03-04 | 18000 | 14000
-- 2025-03-05 | 14000 | 13666.67
-- 2025-03-06 | 21000 | 17666.67

ROWS counts rows, not days. If a day is missing from the table, "2 PRECEDING" reaches further back in time. PostgreSQL 11 and later can use a time range instead: RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW. In SQLite, fill missing days first, for example with a calendar table.

Running totals per group

Combine PARTITION BY with ORDER BY and each store gets its own running total that starts again from zero.

SELECT store, sale_date, revenue,
       SUM(revenue) OVER (PARTITION BY store ORDER BY sale_date) AS store_running
FROM daily_sales
ORDER BY store, sale_date;
-- Mumbai | 2025-03-01 | 20000 | 20000
-- Mumbai | 2025-03-02 | 17000 | 37000
-- ...
-- Pune   | 2025-03-01 | 12000 | 12000
-- Pune   | 2025-03-02 | 15000 | 27000

Common mistakes

• Forgetting ORDER BY inside OVER. SUM(revenue) OVER (PARTITION BY store) is the store total on every row, not a running total.
• Running totals that jump on ties because of the default RANGE frame. Write ROWS and add a tie-breaker.
• Treating the first rows of a moving average as full averages. Show them, but know they cover fewer days; filter with COUNT(*) OVER the same frame = 3 if you need full windows only.
• Assuming ROWS BETWEEN 2 PRECEDING means 2 days. It means 2 rows; missing days break it.
• Mixing up the frame order: it is BETWEEN start AND end, and the start must come first (2 PRECEDING AND CURRENT ROW, not the other way round).