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.

sql
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.

sql
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.

sql
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).

sql
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.

sql
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.

← All SQL entries