Aggregates as Window Functions
`SUM`, `AVG`, `COUNT`, `MIN` and `MAX` with `OVER`: running and sliding totals, share of total, `FILTER` (PostgreSQL, SQLite, ClickHouse but not MySQL), windows over `GROUP BY` output and the self-join they replace.
The problem
Running totals, moving averages and "share of total" used to be a self-join or a correlated subquery per row. `SUM`, `AVG`, `COUNT`, `MIN` and `MAX` with `OVER` compute them in one sorted pass. The questions are which rows the frame covers (ties!), how NULLs and empty frames behave, whether `FILTER` is available, and whether the aggregate sits over raw rows or over `GROUP BY` output.
How it works
- Four shapes. `SUM(x) OVER ()`: the grand total on every row. `SUM(x) OVER (PARTITION BY c)`: the group total on every row. `SUM(x) OVER (PARTITION BY c ORDER BY d, id)`: a running total per group. Add `ROWS BETWEEN 1 PRECEDING AND CURRENT ROW` for a sliding window. For customer `ann` (50, 30, 20) the running sum is 50, 80, 100; the count so far 1, 2, 3; the customer total 100; the share 50.0, 30.0, 20.0; the average of the last two 50, 40, 25; the max of the last two 50, 50, 30 (first snippet, same on all four engines).
- Ties follow the frame default: with `ORDER BY day` alone, rows sharing a day get the same running value because they are peers; `ORDER BY day, id` (or `ROWS`) steps row by row. For ledgers, always add a unique tie-breaker, or accept that same-day rows move together. The correlated-subquery form with `q.day <= p.day` reproduces the peer-inclusive result exactly (fourth snippet).
- NULL handling: `SUM`, `AVG`, `MIN`, `MAX` and `COUNT(x)` skip NULL values, `COUNT(*)` counts rows. An empty frame (the next two rows on the last row) gives NULL for `SUM`, `AVG`, `MAX` and 0 for `COUNT`, identically on all four engines (fifth snippet, where `v` is declared `INT NULL`; a non-Nullable ClickHouse column gives 0 for `SUM` and `MAX` and nan for `AVG`). `AVG` divides by the non-NULL count in the frame: with v = 10, 20, 30, NULL, 50 the next-two average at id 3 is 50 (only one non-NULL value).
- Share of total: `100.0 * amount / SUM(amount) OVER (PARTITION BY customer)`. Use `100.0` rather than `100` to avoid integer division, and wrap the divisor in `NULLIF(..., 0)` when a total can be 0. The result for `bob` is 33.3, 8.3, 58.3 (`ROUND(..., 1)`).
- Windows over `GROUP BY` output: `SUM(SUM(amount)) OVER ()` is the grand total of the per-group sums and `RANK() OVER (ORDER BY SUM(amount) DESC)` ranks the groups (second snippet: `bob | 120 | 54.5 | 1`, `ann | 100 | 45.5 | 2`). Order of evaluation: group first, window second.
- `FILTER (WHERE cond)` restricts which rows a window aggregate sees: `SUM(amount) FILTER (WHERE amount >= 40) OVER (PARTITION BY customer ORDER BY day, id)`. It runs on PostgreSQL, SQLite and ClickHouse (third snippet). MySQL 8.0.46 raises `ERROR 1064 (42000)` (syntax error near `(WHERE ...`). The portable spelling is `SUM(CASE WHEN amount >= 40 THEN amount END) OVER (...)`, which returns the same numbers (second snippet, second query).
- `DISTINCT` inside a window aggregate: PostgreSQL `DISTINCT is not implemented for window functions`; MySQL `ERROR 1235 (42000): This version of MySQL doesn't yet support '<window function>(DISTINCT ..)'`; SQLite `DISTINCT is not supported for window functions`; ClickHouse accepts it. Elsewhere, deduplicate in a subquery first.
- Versus the old forms: `SUM(x) OVER (ORDER BY day)` equals `(SELECT SUM(b.x) FROM t b WHERE b.day <= a.day)` and equals `SELF JOIN ... ON b.day <= a.day GROUP BY a.id`, all giving 10, 50, 50, ... on the tie data. The window form needs one sort; the other two pair every row with every earlier row. On PostgreSQL the plan is a `WindowAgg` over a `Sort`, against a `SubPlan` that scans the table once per row.
- Forward-looking and centred windows work the same way: `ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING` is the next two rows, `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` a centred three-row window (see `window-frames` for the full bound rules).
Examples
Running total, running count, partition total, share of partition, and a two-row sliding average and maximum, per customer. Numbers are shown as PostgreSQL prints them; other clients may drop trailing zeros.
CREATE TABLE payments (id INT, customer VARCHAR(10), day DATE, amount INT);
INSERT INTO payments VALUES (1,'ann','2026-02-01',50),(2,'ann','2026-02-03',30),(3,'ann','2026-02-03',20),(4,'bob','2026-02-02',40),(5,'bob','2026-02-04',10),(6,'bob','2026-02-05',70);
SELECT id, customer, amount,
SUM(amount) OVER (PARTITION BY customer ORDER BY day, id) AS running,
COUNT(*) OVER (PARTITION BY customer ORDER BY day, id) AS n_so_far,
SUM(amount) OVER (PARTITION BY customer) AS cust_total,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer), 1) AS pct_of_cust,
ROUND(AVG(amount) OVER (PARTITION BY customer ORDER BY day, id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW), 1) AS avg_last2,
MAX(amount) OVER (PARTITION BY customer ORDER BY day, id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS max_last2
FROM payments
ORDER BY id;
-- => id | customer | amount | running | n_so_far | cust_total | pct_of_cust | avg_last2 | max_last2
-- => 1 | ann | 50 | 50 | 1 | 100 | 50.0 | 50.0 | 50
-- => 2 | ann | 30 | 80 | 2 | 100 | 30.0 | 40.0 | 50
-- => 3 | ann | 20 | 100 | 3 | 100 | 20.0 | 25.0 | 30
-- => 4 | bob | 40 | 40 | 1 | 120 | 33.3 | 40.0 | 40
-- => 5 | bob | 10 | 50 | 2 | 120 | 8.3 | 25.0 | 40
-- => 6 | bob | 70 | 120 | 3 | 120 | 58.3 | 40.0 | 70
-- cleanup
DROP TABLE payments;A window over GROUP BY output (share of all customers, rank by total), then the portable CASE spelling of a filtered window aggregate.
CREATE TABLE payments (id INT, customer VARCHAR(10), day DATE, amount INT);
INSERT INTO payments VALUES (1,'ann','2026-02-01',50),(2,'ann','2026-02-03',30),(3,'ann','2026-02-03',20),(4,'bob','2026-02-02',40),(5,'bob','2026-02-04',10),(6,'bob','2026-02-05',70);
SELECT customer, SUM(amount) AS total,
ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct_of_all,
RANK() OVER (ORDER BY SUM(amount) DESC) AS rk
FROM payments
GROUP BY customer
ORDER BY rk;
-- => customer | total | pct_of_all | rk
-- => bob | 120 | 54.5 | 1
-- => ann | 100 | 45.5 | 2
SELECT id, customer, amount,
SUM(CASE WHEN amount >= 40 THEN amount END) OVER (PARTITION BY customer ORDER BY day, id) AS big_running,
SUM(CASE WHEN amount < 40 THEN 1 ELSE 0 END) OVER (PARTITION BY customer) AS small_count
FROM payments
ORDER BY id;
-- => id | customer | amount | big_running | small_count
-- => 1 | ann | 50 | 50 | 2
-- => 2 | ann | 30 | 50 | 2
-- => 3 | ann | 20 | 50 | 2
-- => 4 | bob | 40 | 40 | 1
-- => 5 | bob | 10 | 40 | 1
-- => 6 | bob | 70 | 110 | 1
-- cleanup
DROP TABLE payments;FILTER on window aggregates: same numbers as the CASE form above. MySQL 8.0 does not accept FILTER.
CREATE TABLE payments (id INT, customer VARCHAR(10), day DATE, amount INT);
INSERT INTO payments VALUES (1,'ann','2026-02-01',50),(2,'ann','2026-02-03',30),(3,'ann','2026-02-03',20),(4,'bob','2026-02-02',40),(5,'bob','2026-02-04',10),(6,'bob','2026-02-05',70);
SELECT id, customer, amount,
SUM(amount) FILTER (WHERE amount >= 40) OVER (PARTITION BY customer ORDER BY day, id) AS big_running,
COUNT(*) FILTER (WHERE amount < 40) OVER (PARTITION BY customer) AS small_count
FROM payments
ORDER BY id;
-- => id | customer | amount | big_running | small_count
-- => 1 | ann | 50 | 50 | 2
-- => 2 | ann | 30 | 50 | 2
-- => 3 | ann | 20 | 50 | 2
-- => 4 | bob | 40 | 40 | 1
-- => 5 | bob | 10 | 40 | 1
-- => 6 | bob | 70 | 110 | 1
-- cleanup
DROP TABLE payments;One window, one correlated subquery and one self-join produce the same running total, ties included (ids 2 and 3 on the same day both show 140).
CREATE TABLE payments (id INT, customer VARCHAR(10), day DATE, amount INT);
INSERT INTO payments VALUES (1,'ann','2026-02-01',50),(2,'ann','2026-02-03',30),(3,'ann','2026-02-03',20),(4,'bob','2026-02-02',40),(5,'bob','2026-02-04',10),(6,'bob','2026-02-05',70);
SELECT p.id, p.day, p.amount,
SUM(p.amount) OVER (ORDER BY p.day) AS by_window,
(SELECT SUM(q.amount) FROM payments q WHERE q.day <= p.day) AS by_subquery
FROM payments p
ORDER BY p.day, p.id;
-- => id | day | amount | by_window | by_subquery
-- => 1 | 2026-02-01 | 50 | 50 | 50
-- => 4 | 2026-02-02 | 40 | 90 | 90
-- => 2 | 2026-02-03 | 30 | 140 | 140
-- => 3 | 2026-02-03 | 20 | 140 | 140
-- => 5 | 2026-02-04 | 10 | 150 | 150
-- => 6 | 2026-02-05 | 70 | 220 | 220
SELECT a.id, SUM(b.amount) AS by_self_join
FROM payments a
JOIN payments b ON b.day <= a.day
GROUP BY a.id
ORDER BY a.id;
-- => id | by_self_join
-- => 1 | 50
-- => 2 | 140
-- => 3 | 140
-- => 4 | 90
-- => 5 | 150
-- => 6 | 220
-- cleanup
DROP TABLE payments;A forward-looking frame: SUM, AVG and MAX are NULL on the empty last frame while COUNT is 0; COUNT(v) skips the NULL, COUNT(*) does not.
CREATE TABLE steps (id INT, v INT NULL);
INSERT INTO steps VALUES (1,10),(2,20),(3,30),(4,NULL),(5,50);
-- forward-looking frame: the next two rows, empty on the last row
SELECT id, v,
SUM(v) OVER (ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS next2_sum,
COUNT(v) OVER (ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS next2_count_v,
COUNT(*) OVER (ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS next2_count_rows,
AVG(v) OVER (ORDER BY id ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS next2_avg
FROM steps
ORDER BY id;
-- => id | v | next2_sum | next2_count_v | next2_count_rows | next2_avg
-- => 1 | 10 | 50 | 2 | 2 | 25.0000000000000000
-- => 2 | 20 | 30 | 1 | 2 | 30.0000000000000000
-- => 3 | 30 | 50 | 1 | 2 | 50.0000000000000000
-- => 4 | NULL | 50 | 1 | 1 | 50.0000000000000000
-- => 5 | 50 | NULL | 0 | 0 | NULL
-- cleanup
DROP TABLE steps;In practice
- PostgreSQL — `FILTER` on aggregates and window aggregates (since 9.4 per the docs); no `DISTINCT` inside window aggregates (observed on 16.15).
- MySQL — No `FILTER` (`ERROR 1064`) and no `DISTINCT` in window aggregates (`ERROR 1235`); use `CASE` inside the aggregate (observed on 8.0.46).
- SQLite — `FILTER` accepted on window aggregates in 3.45.1; `DISTINCT` rejected.
- ClickHouse — `FILTER` and `COUNT(DISTINCT x) OVER` both run; the `-If` combinators (`sumIf`) are the native alternative to `FILTER` for plain aggregation (observed on 26.9).
- Django ORM — `Sum("amount", filter=Q(...))` maps to `FILTER` where the backend supports it and to a `CASE` expression where it does not, such as MySQL; wrapping the aggregate in `Window(...)` adds the `OVER` clause (per the Django documentation; not run here).
Trade-offs
Pros
- One sorted pass replaces a correlated subquery or self-join per row.
- The same frame syntax gives running, sliding, centred and forward-looking aggregates.
- Composes with `GROUP BY` for shares and ranks of aggregates.
Cons
- Peers move together under the default frame, which is wrong for ledgers unless a unique tie-breaker is added.
- `FILTER` and `DISTINCT` support differ per engine, forcing `CASE` rewrites on MySQL.
- Every distinct `PARTITION BY`/`ORDER BY` costs a sort, and wide frames over large partitions still touch many rows.