Running totals in SQL — one OVER clause, shown on real orders

A running total is SUM(x) OVER (ORDER BY ...) — adding ORDER BY inside OVER turns a plain sum into a cumulative one. No CUMSUM function exists in SQL; the ordered window is the cumsum. Here it is on real order data, then per-customer, then the two traps: ties and the pre-window self-join you will still meet in old code.

The basic running total

SELECT order_date, total,
  ROUND(SUM(total) OVER (ORDER BY order_date, order_id), 2) AS running_total
FROM orders
ORDER BY order_date, order_id;

Each row shows its own total plus everything before it in the ordering. Every row survives — that is the window-function contract: GROUP BY would have collapsed these 40 orders into buckets; OVER annotates them in place.

Per-group running totals: add PARTITION BY

SELECT customer_id, order_date, total,
  SUM(total) OVER (
    PARTITION BY customer_id      -- restart per customer
    ORDER BY order_date, order_id  -- accumulate in time order
  ) AS customer_running
FROM orders;

This is the shape of half the "running" questions in practice: running balance per account, cumulative points per player, spend-to-date per customer. PARTITION BY picks the lane, ORDER BY picks the direction, SUM accumulates.

The tie trap: why order_id is in the ORDER BY

The self-join it replaced (still in old code)

SELECT a.order_date, a.total, SUM(b.total) AS running_total
FROM orders a
JOIN orders b ON b.order_id <= a.order_id
GROUP BY a.order_id, a.order_date, a.total;

Every row joins to all rows before it — 40 orders make 820 pairs; a million orders make half a trillion. You will meet this in legacy reports and old Stack Overflow answers. Recognize it, replace it with the OVER version, take the credit.

▶ Practice: Running Total of Orders

FAQ

How do I do a running total that resets each month?

Partition by the month: PARTITION BY strftime('%Y-%m', order_date) ORDER BY order_date. Reset-per-X is always "X goes in PARTITION BY".

Running average? Running max?

Same skeleton, different aggregate: AVG(total) OVER (ORDER BY ...), MAX(total) OVER (ORDER BY ...). For moving (last-7-rows) versions, add a frame: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.

Percent of total to date?

Divide the running sum by the grand total: SUM(total) OVER (ORDER BY d) * 100.0 / SUM(total) OVER () — the empty OVER () is the whole-table sum. This is the Pareto/cumulative-share pattern interviews like.