Running Totals & Moving Averages
Running totals (also called cumulative sums) and moving averages are two of the most common analytical calculations, and window functions make both straightforward — no self-joins, no procedural loops, no application-side post-processing required.
Running totals with SUM() OVER
A running total adds up a value from the start of an ordered sequence up through the current row. Pairing SUM() with an OVER clause that has an ORDER BY (and no PARTITION BY, if the total should span the whole table) produces exactly that.
Given daily sales figures:
sale_date | amount -----------+------- 2024-01-01 | 200 2024-01-02 | 150 2024-01-03 | 300 2024-01-04 | 100
SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS running_total FROM daily_sales;
sale_date | amount | running_total -----------+--------+-------------- 2024-01-01 | 200 | 200 2024-01-02 | 150 | 350 2024-01-03 | 300 | 650 2024-01-04 | 100 | 750
Each row's running_total is the sum of every amount from the earliest date up to and including that row's own date. This works because, when ORDER BY is present without an explicit frame clause, SQL defaults the frame to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — exactly the range a cumulative sum needs.
Moving averages with a bounded frame
A moving average (also called a rolling average) only looks at a fixed-size window of recent rows rather than the entire history. This is achieved with an explicit ROWS BETWEEN frame that limits how far back the calculation reaches.
AVG(amount) OVER ( ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
This computes a 7-day moving average: the current row plus the 6 rows before it, averaged together (7 rows total). Early rows near the start of the data, where fewer than 6 prior rows exist, simply average over however many rows are actually available.
Worked example: 7-day moving average
Extending the daily sales table to 10 days:
sale_date | amount -----------+------- 2024-01-01 | 200 2024-01-02 | 150 2024-01-03 | 300 2024-01-04 | 100 2024-01-05 | 250 2024-01-06 | 220 2024-01-07 | 180 2024-01-08 | 260 2024-01-09 | 240 2024-01-10 | 210
SELECT
sale_date,
amount,
ROUND(
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2
) AS moving_avg_7d
FROM daily_sales;sale_date | amount | moving_avg_7d -----------+--------+-------------- 2024-01-01 | 200 | 200.00 2024-01-02 | 150 | 175.00 2024-01-03 | 300 | 216.67 2024-01-04 | 100 | 187.50 2024-01-05 | 250 | 200.00 2024-01-06 | 220 | 203.33 2024-01-07 | 180 | 200.00 2024-01-08 | 260 | 208.57 2024-01-09 | 240 | 208.57 2024-01-10 | 210 | 208.57
From 2024-01-07 onward, every row's average is computed from a full 7-day window (the current day plus the previous 6). Before that, the window simply contains fewer days — for example, 2024-01-01 has no prior days at all, so its moving average equals its own amount.
Goal | Function + frame |
|---|---|
Running total (all history) | SUM(x) OVER (ORDER BY date) |
Running total per group | SUM(x) OVER (PARTITION BY grp ORDER BY date) |
N-row moving average | AVG(x) OVER (ORDER BY date ROWS BETWEEN N-1 PRECEDING AND CURRENT ROW) |
Centered moving average | AVG(x) OVER (ORDER BY date ROWS BETWEEN N PRECEDING AND N FOLLOWING) |