Unit 07.03: Running totals and why ORDER BY is mandatory

Unit ID: SQL-M07-U04 - Estimated active time: 14-17 minutes Objective: build a cumulative total that is deterministic, and explain the frame it implies.

ORDER BY inside OVER creates the accumulation

SELECT d,
       daily,
       SUM(daily) OVER (ORDER BY d) AS running_total
FROM (
  SELECT CAST(placed_at AS DATE) AS d, SUM(order_total) AS daily
  FROM orders
  GROUP BY d
) t
ORDER BY d
LIMIT 5;
-- 2026-06-01 | 100968.00 | 100968.00
-- 2026-06-02 |  97726.00 | 198694.00
-- 2026-06-03 |  98984.00 | 297678.00
-- 2026-06-04 |  95742.00 | 393420.00
-- 2026-06-05 |  92500.00 | 485920.00

Each running total is the previous one plus that day's revenue. 100,968 + 97,726 = 198,694 - you can verify the accumulation by hand, which is worth doing once.

Without ORDER BY there is no accumulation

SELECT d, daily, SUM(daily) OVER () AS not_a_running_total
FROM (SELECT CAST(placed_at AS DATE) AS d, SUM(order_total) AS daily FROM orders GROUP BY d) t
ORDER BY d
LIMIT 3;

Every row now shows the same grand total. ORDER BY inside OVER is what turns "all rows" into "all rows up to this one". It is not decoration - it is the entire mechanism.

The implied frame

SUM(x) OVER (ORDER BY d) is shorthand for:

-- fragment: window clause shown on its own
SUM(x) OVER (ORDER BY d RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

"From the start of the partition up to the current row." Knowing the default exists matters because you can change it - a 7-day moving average uses a different frame:

SELECT d,
       daily,
       ROUND(AVG(daily) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 2) AS moving_avg_7d
FROM (SELECT CAST(placed_at AS DATE) AS d, SUM(order_total) AS daily FROM orders GROUP BY d) t
ORDER BY d
LIMIT 8;

The first six rows average fewer than seven days, because the frame is simply shorter at the start. That is correct behaviour and a common source of "why is the early data wrong?" - it is not wrong, it is incomplete, and the chart should say so.

The determinism trap again

If the ORDER BY column has ties, the running total at those rows is ambiguous - the same problem as Unit 07.03, now affecting a cumulative figure. Order by something unique, or accept that tied rows may accumulate in either order.

Practice

Build a running total of completed revenue by day, and state what the final row should equal.

SELECT d, daily, SUM(daily) OVER (ORDER BY d) AS running_total
FROM (
  SELECT CAST(placed_at AS DATE) AS d, SUM(order_total) AS daily
  FROM orders WHERE status = 'completed'
  GROUP BY d
) t
ORDER BY d;

Takeaway

ORDER BY inside OVER creates the accumulation and implies a frame from the start to the current row. Ties in that ordering make the running total ambiguous.

---

Mark complete