MENU


Aggregation and Window Functions

"What did we sell each month?" collapses rows into one per group: the aggregate functions with GROUP BY (Section 3.5.4). "What is the running total?" keeps every row and computes across its neighbors: the window functions, in MySQL since 8.0.

3.8.1 COUNT, SUM, AVG, MIN, and MAX

An aggregate returns one value per group, and without GROUP BY the whole result is one group. Revenue per month from March to May, then the dearest title per category:

SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS month, COUNT(DISTINCT o.id) AS orders,

COUNT(*) AS line_items, SUM(i.quantity * i.unit_price) AS revenue,

ROUND(AVG(i.unit_price), 2) AS avg, MIN(i.unit_price) AS min, MAX(i.unit_price) AS max

FROM orders AS o JOIN order_items AS i ON i.order_id = o.id

WHERE o.status <> 'cancelled' AND o.ordered_at >= '2026-03-01' AND o.ordered_at < '2026-06-01'

GROUP BY month ORDER BY month;

SELECT category_id, title, MAX(price) FROM products GROUP BY category_id;

Listing 3.68: Revenue per month, then a column that is neither grouped nor aggregated

+---------+--------+------------+---------+-------+-------+-------+

| month | orders | line_items | revenue | avg | min | max |

+---------+--------+------------+---------+-------+-------+-------+

| 2026-03 | 2 | 5 | 153.37 | 26.18 | 4.99 | 44.50 |

| 2026-04 | 1 | 1 | 49.00 | 49.00 | 49.00 | 49.00 |

| 2026-05 | 1 | 2 | 86.50 | 43.25 | 42.00 | 44.50 |

+---------+--------+------------+---------+-------+-------+-------+

3 rows in set (0.001 sec)

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains

nonaggregated column 'shop.products.title' which is not functionally dependent on columns in

GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Output of Listing 3.68 COUNT(*) counts joined lines and COUNT(DISTINCT o.id) orders. Other aggregates skip NULL (COUNT(body) over reviews is 6 of 7), and on an empty set all but COUNT return NULL, so write COALESCE(SUM(x), 0). The error protects you: servers before 5.7.5 paired MAX(price) with a title from an arbitrary row. ANY_VALUE(title) hides that; a ranking function (Section 3.8.5) fixes it.

3.8.2 GROUP_CONCAT and Aggregating into JSON

GROUP_CONCAT([DISTINCT] expr [ORDER BY ...] [SEPARATOR str]) joins a group's values into a string. JSON_ARRAYAGG(expr) and JSON_OBJECTAGG(key, value) build the JSON a PHP endpoint returns:

SELECT c.name AS category, GROUP_CONCAT(p.id ORDER BY p.price DESC) AS by_price,

JSON_ARRAYAGG(p.id) AS ids, JSON_OBJECTAGG(p.sku, p.stock) AS stock

FROM categories AS c JOIN products AS p ON p.category_id = c.id

GROUP BY c.id ORDER BY c.id;

Listing 3.69: Each category's products as a string, a JSON array and a JSON object

+-------------+----------+-----------+----------------------------------------------------+

| category | by_price | ids | stock |

+-------------+----------+-----------+----------------------------------------------------+

| Programming | 4,6,1 | [1, 4, 6] | {"BK-LAR-01": 18, "BK-LNX-01": 9, "BK-PHP-01": 25} |

| Databases | 2,3 | [2, 3] | {"BK-SQL-01": 12, "BK-SQL-02": 0} |

| Design | 5 | [5] | {"BK-UX-01": 7} |

| Accessories | 7,8 | [7, 8] | {"AC-MUG-01": 60, "AC-STK-01": 150} |

+-------------+----------+-----------+----------------------------------------------------+

4 rows in set (0.000 sec)

Output of Listing 3.69 Only GROUP_CONCAT takes ORDER BY; the array's order is undefined, and JSON_ARRAYAGG(id ORDER BY id) is syntax error 1064. JSON_OBJECTAGG keeps a duplicate key's last value. GROUP_CONCAT silently stops at group_concat_max_len (1,024 bytes by default, warning 1260), so raise it per session.

3.8.3 WHERE Versus HAVING

WHERE filters rows before grouping, so WHERE SUM(...) > 100 fails with ERROR 1111 (HY000): Invalid use of group function. HAVING filters groups once the aggregates, and any ROLLUP rows, exist. Revenue by department and category, with subtotals:

SELECT pc.name AS department, c.name AS category, SUM(i.quantity * i.unit_price) AS revenue

FROM order_items AS i JOIN orders AS o ON o.id = i.order_id

JOIN products AS p ON p.id = i.product_id JOIN categories AS c ON c.id = p.category_id

JOIN categories AS pc ON pc.id = COALESCE(c.parent_id, c.id) WHERE o.status <> 'cancelled'

GROUP BY department, category WITH ROLLUP

HAVING GROUPING(category) = 1 OR revenue > 100;

Listing 3.70: WHERE drops rows, HAVING drops groups, even ROLLUP ones

+-------------+-------------+---------+

| department | category | revenue |

+-------------+-------------+---------+

| Accessories | NULL | 94.96 |

| Books | Databases | 162.50 |

| Books | Programming | 350.70 |

| Books | NULL | 513.20 |

| NULL | NULL | 608.16 |

+-------------+-------------+---------+

5 rows in set (0.001 sec)

Output of Listing 3.70 The Accessories row (94.96) failed revenue > 100, but its subtotal survived: GROUPING(category) is 1 on super-aggregate rows. Design is absent because WHERE removed its cancelled order before grouping. Keep conditions that need no aggregate in WHERE, where they can use an index.

3.8.4 The OVER Clause and Partitioning

OVER (...) turns an aggregate into a window function that answers on every row instead of collapsing them. OVER () is one window over the whole result; PARTITION BY splits it into independent windows, like GROUP BY without the collapse:

SELECT category_id AS cat, sku, price,

ROUND(AVG(price) OVER (PARTITION BY category_id), 2) AS cat_avg,

ROUND(100 * price / SUM(price) OVER (), 1) AS pct

FROM products WHERE category_id IN (2, 5) ORDER BY cat, price DESC;

Listing 3.71: Each product beside its category average and its share of the result

+-----+-----------+-------+---------+------+

| cat | sku | price | cat_avg | pct |

+-----+-----------+-------+---------+------+

| 2 | BK-LAR-01 | 49.00 | 43.63 | 33.0 |

| 2 | BK-LNX-01 | 42.00 | 43.63 | 28.3 |

| 2 | BK-PHP-01 | 39.90 | 43.63 | 26.9 |

| 5 | AC-MUG-01 | 12.50 | 8.75 | 8.4 |

| 5 | AC-STK-01 | 4.99 | 8.75 | 3.4 |

+-----+-----------+-------+---------+------+

5 rows in set (0.001 sec)

Output of Listing 3.71 The shares total 100 over these five rows, not the catalog: windows run after WHERE, GROUP BY and HAVING (Section 3.5.1). So RANK() OVER (...) in WHERE fails with ERROR 3593 (HY000): You cannot use the window function 'rank' in this context.; compute it in a CTE and filter outside. COUNT(DISTINCT ...) OVER and a windowed GROUP_CONCAT fail with error 1235.

3.8.5 Ranking with ROW_NUMBER, RANK, and DENSE_RANK

The ranking functions number each partition's rows in the window's ORDER BY and differ on ties. A view (Section 3.15.7) of units sold per product, reused below, has three titles tied at 3:

CREATE VIEW units_sold AS SELECT p.id, p.sku, p.category_id, SUM(i.quantity) AS units

FROM products AS p JOIN order_items AS i ON i.product_id = p.id

JOIN orders AS o ON o.id = i.order_id AND o.status <> 'cancelled' GROUP BY p.id;

SELECT category_id AS cat, sku, units, ROW_NUMBER() OVER (ORDER BY units DESC) AS row_num,

RANK() OVER (ORDER BY units DESC) AS rnk,

DENSE_RANK() OVER (ORDER BY units DESC) AS dense,

RANK() OVER (PARTITION BY category_id ORDER BY units DESC) AS in_cat

FROM units_sold ORDER BY units DESC, row_num;

Listing 3.72: Three ways to number tied rows, and a rank within each category

Query OK, 0 rows affected (0.013 sec)

+-----+-----------+-------+---------+-----+-------+--------+

| cat | sku | units | row_num | rnk | dense | in_cat |

+-----+-----------+-------+---------+-----+-------+--------+

| 5 | AC-MUG-01 | 6 | 1 | 1 | 1 | 1 |

| 5 | AC-STK-01 | 4 | 2 | 2 | 2 | 2 |

| 2 | BK-PHP-01 | 3 | 3 | 3 | 3 | 1 |

| 3 | BK-SQL-01 | 3 | 4 | 3 | 3 | 1 |

| 2 | BK-LAR-01 | 3 | 5 | 3 | 3 | 1 |

| 2 | BK-LNX-01 | 2 | 6 | 6 | 4 | 3 |

| 3 | BK-SQL-02 | 1 | 7 | 7 | 5 | 2 |

+-----+-----------+-------+---------+-----+-------+--------+

7 rows in set (0.001 sec)

Output of Listing 3.72 ROW_NUMBER never repeats, so its order among ties is arbitrary unless you add a tiebreaker such as id. RANK shares a number and skips; DENSE_RANK shares without skipping. Wrap the query in a CTE and keep in_cat = 1, and you have the top product per category Section 3.8.1 could not write, both tied Programming titles included.

3.8.6 LAG, LEAD, FIRST_VALUE, and NTILE

LAG(expr [, n [, default]]) reads the value n rows back (1 by default) and LEAD reads forward, returning default or NULL past the edge. FIRST_VALUE, LAST_VALUE and NTH_VALUE(expr, n) read a position in the frame; NTILE(n) deals rows into n buckets. Over a monthly view:

CREATE VIEW monthly_revenue AS SELECT DATE_FORMAT(o.ordered_at, '%Y-%m') AS month,

SUM(i.quantity * i.unit_price) AS revenue FROM orders AS o JOIN order_items AS i

ON i.order_id = o.id WHERE o.status <> 'cancelled' GROUP BY month;

SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev,

revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS delta,

LEAD(revenue) OVER (ORDER BY month) AS next,

FIRST_VALUE(revenue) OVER (ORDER BY month) AS first,

NTILE(3) OVER (ORDER BY revenue DESC) AS tier FROM monthly_revenue ORDER BY month;

Listing 3.73: Month-over-month revenue with offsets, the first month and three tiers

Query OK, 0 rows affected (0.014 sec)

+---------+---------+--------+---------+--------+--------+------+

| month | revenue | prev | delta | next | first | tier |

+---------+---------+--------+---------+--------+--------+------+

| 2026-03 | 153.37 | NULL | 153.37 | 49.00 | 153.37 | 1 |

| 2026-04 | 49.00 | 153.37 | -104.37 | 86.50 | 153.37 | 3 |

| 2026-05 | 86.50 | 49.00 | 37.50 | 128.80 | 153.37 | 1 |

| 2026-06 | 128.80 | 86.50 | 42.30 | 50.00 | 153.37 | 1 |

| 2026-07 | 50.00 | 128.80 | -78.80 | 86.50 | 153.37 | 3 |

| 2026-08 | 86.50 | 50.00 | 36.50 | 53.99 | 153.37 | 2 |

| 2026-09 | 53.99 | 86.50 | -32.51 | NULL | 153.37 | 2 |

+---------+---------+--------+---------+--------+--------+------+

7 rows in set (0.001 sec)

Output of Listing 3.73 The default 0 makes March's delta its whole revenue. Partitioned by customer, LAG gives each customer's gap between orders. NTILE(3) deals seven rows as 3, 2 and 2 and splits the two 86.50 months across tiers, because it counts rows, not values. LAG, LEAD, NTILE and the ranking functions ignore frames; FIRST_VALUE and LAST_VALUE obey them.

3.8.7 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:

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;

Listing 3.74: Each customer's running spend, order by order

+------+------------+-------+---------+

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

Output of Listing 3.74 The frame restarts in each partition. The figure shows four frames over Section 3.8.5's order:Figure 3.7: Four frames around the same current row, and the SUM(units) each producesWithout 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.

3.8.8 Named Windows and Practical Reporting Patterns

A WINDOW clause after HAVING names a window, and OVER w reuses it. OVER (w ...) may add a frame but not a second ORDER BY (error 3583). A monthly dashboard in one statement:

SELECT month, revenue, SUM(revenue) OVER w AS running,

ROUND(AVG(revenue) OVER (w ROWS 2 PRECEDING), 2) AS avg_3m,

ROUND(100 * SUM(revenue) OVER w / SUM(revenue) OVER (), 1) AS pct_to_date

FROM monthly_revenue WINDOW w AS (ORDER BY month);

Listing 3.75: Running total, three-month moving average and cumulative share of revenue

+---------+---------+---------+--------+-------------+

| month | revenue | running | avg_3m | pct_to_date |

+---------+---------+---------+--------+-------------+

| 2026-03 | 153.37 | 153.37 | 153.37 | 25.2 |

| 2026-04 | 49.00 | 202.37 | 101.19 | 33.3 |

| 2026-05 | 86.50 | 288.87 | 96.29 | 47.5 |

| 2026-06 | 128.80 | 417.67 | 88.10 | 68.7 |

| 2026-07 | 50.00 | 467.67 | 88.43 | 76.9 |

| 2026-08 | 86.50 | 554.17 | 88.43 | 91.1 |

| 2026-09 | 53.99 | 608.16 | 63.50 | 100.0 |

+---------+---------+---------+--------+-------------+

7 rows in set (0.001 sec)

Output of Listing 3.75 The default RANGE frame is safe here because months are unique. Other reports reuse the shapes: top N per group ranks in a CTE and filters outside (Section 3.8.5); period over period is LAG; deduplication deletes WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS n FROM t) AS d WHERE n > 1), since DELETE cannot use a window itself.