Aggregation and window functions

Collapsing rows: the aggregate functions

An aggregate function reads many rows and returns one value. The five that matter are COUNT, SUM, AVG, MIN and MAX. Without a GROUP BY they treat the whole result as one group, so the query returns one row. MIN and MAX also work on dates and strings, which is how you find a customer's first or latest order.

NULL is the detail returning people forget. COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. The others skip NULLs, so an average is the average of the values that exist. DISTINCT inside the parentheses counts each distinct value once. An aggregate over zero rows returns NULL, except COUNT, which returns 0, so wrap sums in COALESCE(SUM(...), 0) when a report must show a zero.

SELECT COUNT(*)                    AS all_orders,
       COUNT(customer_id)          AS with_customer,
       COUNT(DISTINCT customer_id) AS customers,
       SUM(total)                  AS revenue,
       ROUND(AVG(total), 2)        AS avg_order,
       MIN(total)                  AS smallest,
       MAX(total)                  AS largest
FROM orders;

GROUP BY and HAVING

GROUP BY splits the rows into groups that share a value, runs the aggregates once per group, and returns one row per group. NULL is a group of its own. WHERE runs before grouping, so it sees single rows and cannot mention an aggregate. HAVING runs after, so it can say SUM(total) > 100. When a condition fits either place, put it in WHERE: fewer rows reach the grouping.

SELECT customer_id, SUM(total) AS paid_total
FROM orders
WHERE status = 'paid'            -- rows, before grouping
GROUP BY customer_id
HAVING SUM(total) > 100;         -- groups, after aggregation

The guest order was paid, so it passed the WHERE and formed its own group of 20.00, which HAVING then removed.

The rule every returning person forgets

Every column in the SELECT list must appear in GROUP BY or sit inside an aggregate. MySQL 8.4, 9.7 and 26.x enforce it through ONLY_FULL_GROUP_BY in the default sql_mode and reject the query with error 1055. PostgreSQL has always rejected it. Both allow one shortcut, called functional dependence: group by a table's primary key and you may select its other columns, because one key value means one row.

-- Rejected: order_date is neither grouped nor aggregated
SELECT customer_id, order_date, SUM(total) FROM orders GROUP BY customer_id;

-- Accepted: order_date is aggregated
SELECT customer_id, MAX(order_date) AS last_order, SUM(total) AS spent
FROM orders GROUP BY customer_id;

-- Accepted: c.name depends on the primary key c.id
SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id;

Window functions: keep every row

A window function computes something over a set of rows related to the current row and writes the answer onto that row. Nothing collapses: five rows in, five rows out, one column added. The phrase to listen for is "per row, compared with the others". MySQL has had them since 8.0, PostgreSQL for over a decade, and the syntax is the same.

The OVER clause has three optional parts. PARTITION BY says which rows belong together, and it is GROUP BY without the collapsing. ORDER BY sets the order inside each partition. The frame says which neighbors the function may see, for example ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Adding an ORDER BY changes the default frame to the start of the partition through the current row, which is why a SUM that showed the partition total starts showing a running total.

ROW_NUMBER is the workhorse. Partition by customer, order by date, and each customer's orders are numbered from their first.

SELECT customer_id, id, order_date, total,
       ROW_NUMBER() OVER (PARTITION BY customer_id
                          ORDER BY order_date) AS n
FROM orders;

The ones worth recognizing:

A running total, on something other than orders

The sample database ships with a small tracks table, because a list with a natural order shows a running total better than invoices do. Here is Jeff Buckley's Grace, with the elapsed time of the album as each song ends.

SELECT track_no, title, seconds,
       SUM(seconds) OVER (ORDER BY track_no) AS elapsed
FROM tracks
WHERE artist = 'Jeff Buckley' AND album = 'Grace'
ORDER BY track_no;

Every row survives and each one carries the total so far. The last value, 3104 seconds, is the length of the album. Drop the ORDER BY inside OVER and every row would show 3104 instead, because the frame becomes the whole partition.

MySQL versus PostgreSQL

These are the lines to translate when a query moves between engines (chapter 17). One trap is silent: MySQL GROUP_CONCAT truncates at group_concat_max_len, 1024 bytes by default, without an error.

Report patterns you will be asked for

Most requests are one of these shapes. Each names an intermediate result with a CTE, the WITH clause (chapter 13).

• Top-N per group. Number the rows with ROW_NUMBER inside each partition, then keep rn = 1; LIMIT applies to the whole result and cannot do this.
• Running total. SUM(total) OVER (ORDER BY order_date).
• Percent of total. Divide the row's value by SUM(value) OVER (), or by a partitioned sum for the share within a group.
• Month over month. Group into months in a CTE, then LAG over the aggregate.
• Deduplicate. Number the rows within each set of duplicates, delete those above 1, then add the unique constraint (chapter 3).

Performance, and where reports should live

A GROUP BY over a big table reads every row unless the WHERE narrows it first, and an index whose column order matches the GROUP BY or the window's ORDER BY lets the engine skip the sort. Chapter 10 (Indexes and query performance) shows those steps in a plan. When the same heavy report runs all day, store the result: PostgreSQL has materialized views, and MySQL needs a summary table filled by a scheduled event (chapter 12).

Go deeper

• MySQL 8.4: Window Functions

  Frame rules, named windows, function list.

• MySQL 8.4: MySQL Handling of GROUP BY

  ONLY_FULL_GROUP_BY, functional dependence, ANY_VALUE().

• MySQL 8.4: GROUP BY Modifiers

  WITH ROLLUP and GROUPING().

• PostgreSQL: Window Functions tutorial

  The gentlest official introduction.

• PostgreSQL: Aggregate Functions

  string_agg, json_agg and FILTER.