Aggregates as Windows

SUM, AVG, COUNT and friends take an OVER clause, replacing self-joins and correlated subqueries with a single pass.

Syntax{SUM | AVG | COUNT | MIN | MAX | ...}(expr) OVER ([PARTITION BY ...] [ORDER BY ...] [frame_clause])

Most aggregate functions become window functions simply by adding an OVER clause. In MySQL 8.4 that applies to AVG(), BIT_AND(), BIT_OR(), BIT_XOR(), COUNT(), JSON_ARRAYAGG(), JSON_OBJECTAGG(), MAX(), MIN(), STDDEV_POP() (and its aliases STDDEV(), STD()), STDDEV_SAMP(), SUM(), VAR_POP() (alias VARIANCE()) and VAR_SAMP(). The function is the same either way; only the presence of OVER decides whether it reduces rows or annotates them.

What they replace

The classic longhand for "each row plus its group's figure" is a correlated subquery or a self-join to a grouped derived table. The window form computes it in one pass and, unlike the subquery, sees exactly the rows the outer WHERE kept — a difference that quietly changes results when a filter is added later.

Two restrictions worth knowing

DISTINCT cannot be combined with OVER, so COUNT(DISTINCT x) OVER (...) is rejected; count distinct values in a grouped subquery first. GROUP_CONCAT() has no OVER form at all — it is not in the list above. MySQL also has no FILTER clause, so conditional aggregates are written with CASE inside the function, relying on COUNT(expr) and SUM(expr) ignoring NULL.

Example

Example · sql
-- Each line of an order, next to the order's own totals.
SELECT
  li.order_id,
  li.sku,
  li.line_cents,
  COUNT(*)           OVER w AS lines_in_order,
  SUM(li.line_cents) OVER w AS order_cents,
  MAX(li.line_cents) OVER w AS biggest_line_cents,
  ROUND(AVG(li.line_cents) OVER w)              AS avg_line_cents,
  ROUND(100 * li.line_cents
            / SUM(li.line_cents) OVER w, 1)     AS pct_of_order
FROM order_lines AS li
WHERE li.order_id IN (SELECT id FROM orders WHERE placed_at >= '2026-01-01')
WINDOW w AS (PARTITION BY li.order_id)   -- no ORDER BY: the whole partition
ORDER BY li.order_id, li.line_cents DESC;

When to use it

  • Add a percent-of-total column to a grouped report.
  • Replace a correlated subquery that computes a per-customer average.
  • Count how many of a customer's orders were refunded, on every order row.
  • Compare each row's value to its group's maximum or minimum.

More examples

The correlated subquery it replaces

They are not quite the same answer, and that is the point. The subquery averages every order the customer ever placed; the window averages only the 2026 orders the WHERE kept. Pick deliberately, and use the subquery when you genuinely need a figure over rows the query filtered out.

Example · sql
-- Longhand: a correlated subquery, re-evaluated per outer row.
SELECT o.id, o.customer_id, o.total_cents,
       (SELECT AVG(o2.total_cents)
        FROM orders AS o2
        WHERE o2.customer_id = o.customer_id) AS customer_avg
FROM orders AS o
WHERE o.placed_at >= '2026-01-01';

-- Shorthand: one pass over the rows already in hand.
SELECT o.id, o.customer_id, o.total_cents,
       AVG(o.total_cents) OVER (PARTITION BY o.customer_id) AS customer_avg
FROM orders AS o
WHERE o.placed_at >= '2026-01-01';

Percent of total on a grouped report

The percent-of-total column that otherwise needs a second query or a self-join to a totals subquery. The nested aggregate is legal precisely because windowing happens after grouping.

Example · sql
SELECT
  s.region,
  SUM(s.amount_cents)                          AS region_cents,
  -- GROUP BY runs before windowing, so the window sees one row per region
  -- and the inner SUM is the group aggregate.
  SUM(SUM(s.amount_cents)) OVER ()             AS all_regions_cents,
  ROUND(100 * SUM(s.amount_cents)
            / SUM(SUM(s.amount_cents)) OVER (), 2) AS pct_of_total
FROM sales AS s
WHERE s.sold_at >= '2026-01-01'
GROUP BY s.region
ORDER BY region_cents DESC;

Conditional counts without a FILTER clause

How to express "count only the rows matching a condition" in a window. Use CASE ... THEN 1 END with COUNT when you want a count, and an explicit ELSE 0 with SUM when you want a total, so the sum is never NULL for a group with no matches.

Example · sql
-- MySQL 8.4 has no FILTER clause; CASE inside the aggregate does the same job.
-- COUNT(expr) ignores NULL, so the ELSE branch can simply be omitted.
SELECT
  o.id,
  o.customer_id,
  o.status,
  o.total_cents,
  COUNT(CASE WHEN o.status = 'refunded' THEN 1 END)        OVER w AS refunded_orders,
  COUNT(*)                                                OVER w AS total_orders,
  SUM(CASE WHEN o.status = 'refunded' THEN o.total_cents
           ELSE 0 END)                                    OVER w AS refunded_cents
FROM orders AS o
WINDOW w AS (PARTITION BY o.customer_id);

Related

sql window introsql partition bysql running totalssql sum avgsql correlated

Discussion

  • Be the first to comment on this lesson.