SQL Running Total — one OVER clause, shown on real orders

Skip ahead to practice: solve window function questions in the browser → · or check your SQL interview readiness (10 questions, no signup).

A running total in SQL is SUM(amount) OVER (ORDER BY date_col, id) — the ORDER BY inside OVER makes the sum cumulative, so each row shows its own amount plus every row before it.
SELECT date_col, amount, SUM(amount) OVER (ORDER BY date_col, id) AS running_total FROM t; — no GROUP BY, every row is kept; add PARTITION BY customer_id inside OVER to restart the total per group.

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.

▶ Write it yourself: Running Total of Orders

FAQ

What is a running total in SQL?

A running total (cumulative sum) is a column where each row shows its own value plus the sum of every row before it in a given order — revenue to date, balance after each transaction. In SQL it is a window function: SUM(amount) OVER (ORDER BY date_col, id). No GROUP BY, every row stays in the result, and PARTITION BY restarts the total per group.

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.