Frames and Running Totals

Frames: ROWS, RANGE, and Running Totals

With an ORDER BY, a window aggregate sees a frame that moves with the current row. ROWS counts rows; RANGE compares values, so rows tied with the current one (its peers) come as a set. ROWS UNBOUNDED PRECEDING, start to current row, gives a running total:

Each customer's running spend, order by orderSQL

WITH order_totals AS (
  SELECT o.customer_id, DATE(o.ordered_at) AS day, SUM(i.quantity * i.unit_price) AS total
  FROM orders AS o JOIN order_items AS i ON i.order_id = o.id
  WHERE o.status <> 'cancelled' AND o.customer_id <= 2 GROUP BY o.id)
SELECT customer_id AS cust, day, total,
       SUM(total) OVER (PARTITION BY customer_id ORDER BY day ROWS UNBOUNDED PRECEDING) AS running
FROM order_totals ORDER BY cust, day;

Output

+------+------------+-------+---------+
| cust | day        | total | running |
+------+------------+-------+---------+
|    1 | 2026-03-02 | 64.90 |   64.90 |
|    1 | 2026-05-05 | 86.50 |  151.40 |
|    2 | 2026-03-09 | 88.47 |   88.47 |
|    2 | 2026-09-02 | 53.99 |  142.46 |
+------+------------+-------+---------+
4 rows in set (0.001 sec)

The frame restarts in each partition. The figure shows four frames over Ranking Functions's order:

Four frames around the same current row, and the SUM(units) each produces

Without a frame clause an ORDER BY window uses RANGE UNBOUNDED PRECEDING, which takes every peer: the tied titles all get 19, where ROWS gives 13, 16 and 19 in an order nothing fixed, so use ROWS for running totals unless the sort key is unique. The same default makes LAST_VALUE(x) OVER (ORDER BY month) return each row's own value; end the frame at UNBOUNDED FOLLOWING instead. RANGE also takes intervals: RANGE BETWEEN INTERVAL 30 DAY PRECEDING AND CURRENT ROW. GROUPS, EXCLUDE and IGNORE NULLS fail with error 1235.

MENU

Frames and Running Totals