Aggregation and grouping

GROUP BY is the clause that turns rows into facts: revenue per month, orders per customer, failures per service. It is also where the most convincing wrong numbers come from, because an aggregate over the wrong set of rows is still a number, formatted nicely, with no error beside it. This lesson is the rules the engine enforces, the ones it does not, and the three ways an aggregate lies.

#The rules of GROUP BY

Every column in the SELECT list must be either inside an aggregate function or listed in GROUP BY. The reason is not pedantry: for a group of five orders, "the customer's name" is well defined and "the order's total" is not, and the engine refuses to guess.

SELECT c.id, c.name, date_trunc('month', o.placed_at) AS month,
       COUNT(*) AS orders, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, date_trunc('month', o.placed_at)
ORDER BY month, revenue DESC;

PostgreSQL lets you write GROUP BY c.id alone here, because c.id is the primary key and c.name is functionally dependent on it. MySQL has enforced the standard rule since 5.7 through ONLY_FULL_GROUP_BY; the older behaviour, where it returned an arbitrary value for an ungrouped column, is the origin of a generation of wrong reports and should never be re-enabled.

#WHERE filters rows, HAVING filters groups

WHERE runs before grouping and cannot see aggregates. HAVING runs after and can:

SELECT c.name, SUM(o.total) AS revenue
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.placed_at >= '2026-01-01'          -- which rows count
GROUP BY c.name
HAVING SUM(o.total) > 10000;               -- which groups survive

Put a condition in WHERE whenever it can go there: it shrinks the rows before the expensive part, and it can use an index. A condition in HAVING that does not mention an aggregate is a condition that should have been in WHERE.

#The three lies

The inflated sum. A join to a second one-to-many table multiplies every row before the aggregate runs. A customer with two addresses and three orders contributes six rows, and SUM(total) doubles. The Joins lesson explains the fan-out; the fix is to aggregate first:

WITH spend AS (
  SELECT customer_id, SUM(total) AS revenue, COUNT(*) AS orders
  FROM orders GROUP BY customer_id
)
SELECT c.name, s.revenue, s.orders, a.city
FROM customers c
JOIN spend s ON s.customer_id = c.id
LEFT JOIN addresses a ON a.customer_id = c.id AND a.is_primary;

COUNT(*) versus COUNT(column). COUNT(*) counts rows. COUNT(col) counts rows where col is not NULL. COUNT(DISTINCT col) counts distinct non-NULL values. Three different questions, and the wrong one is off by exactly the number of NULLs, which is the kind of discrepancy that takes a day to trace.

NULL inside aggregates. SUM, AVG, MIN and MAX ignore NULLs. AVG(rating) over ten rows of which four are NULL is the average of six. Usually that is what you want; when it is not, AVG(COALESCE(rating, 0)) says so explicitly. And an aggregate over an empty set is NULL, not zero: SUM(total) for a customer with no orders is NULL, which then poisons any arithmetic it touches. COALESCE(SUM(total), 0) at the edge.

#Conditional aggregates

One pass over the table, several counts:

-- PostgreSQL
SELECT COUNT(*) FILTER (WHERE status = 'PAID')     AS paid,
       COUNT(*) FILTER (WHERE status = 'REFUNDED') AS refunded,
       SUM(total) FILTER (WHERE status = 'PAID')   AS paid_revenue
FROM orders WHERE placed_at >= '2026-01-01';
 
-- MySQL, and portable everywhere
SELECT SUM(status = 'PAID')                        AS paid,
       SUM(status = 'REFUNDED')                    AS refunded,
       SUM(CASE WHEN status = 'PAID' THEN total END) AS paid_revenue
FROM orders WHERE placed_at >= '2026-01-01';

This replaces three queries, or worse, three round trips from application code. CASE WHEN ... THEN total END has no ELSE, so non-matching rows contribute NULL and are ignored, which is exactly right.

#Grouping sets, rollup and cube

A report that wants totals per region, per country within region, and a grand total is three GROUP BY queries glued with UNION ALL, or one:

SELECT region, country, SUM(total) AS revenue
FROM sales
GROUP BY ROLLUP (region, country)
ORDER BY region NULLS LAST, country NULLS LAST;

ROLLUP (a, b) produces groups for (a, b), (a) and (). CUBE produces every combination. GROUPING SETS ((a), (b), ()) names exactly the ones you want. In the subtotal rows the rolled-up column is NULL, and GROUPING(country) returns 1 to distinguish "subtotal" from "a country literally called NULL". PostgreSQL supports all three; MySQL supports WITH ROLLUP and the GROUPING() function, but not CUBE or GROUPING SETS, so a cross-tab there is still a UNION ALL.

#When the aggregate should be a window

GROUP BY collapses rows. If you want each order and the customer's total beside it, that is not a group, it is a window: SUM(total) OVER (PARTITION BY customer_id). Writing it as a GROUP BY subquery joined back to the orders is correct and twice as long. The next-but-one lesson is about those.

#Under the hood: hash aggregate, sort aggregate, and the memory line

The engine has two ways to group. A hash aggregate reads the input once, hashing each row's grouping key into a table of running state (one accumulator per group per aggregate function: a sum, a count, a min); when the input ends it emits one row per entry. It is the default when the planner expects the number of groups to fit in memory, work_mem in PostgreSQL and tmp_table_size in MySQL, and since PostgreSQL 13 it spills to disk in partitions when the estimate was wrong instead of exceeding memory. A sorted (group) aggregate sorts the input on the grouping columns first, then walks it emitting a row each time the key changes; it needs no hash table and is the choice when the input is already sorted, by an index on the grouping columns or by an earlier merge join, or when the group count is huge. EXPLAIN names them HashAggregate and GroupAggregate; MySQL shows Using temporary for the hash-like path and Using filesort for the sorted one, which is why those two words in Extra are the ones to notice on a big table.

Where the grouping happens in the pipeline explains the WHERE/HAVING rule and the fan-out. Conceptually the order is FROM and joins → WHERE → GROUP BY → aggregates → HAVING → SELECT expressions → ORDER BY → LIMIT. WHERE runs on joined rows before any accumulator exists, so it cannot see a SUM; HAVING runs on the finished groups. And because the joins run first, a one-to-many join has already multiplied the rows by the time the accumulator sees them: the aggregate is correct over the rows it was given, and the rows were wrong. A HAVING condition that mentions no aggregate is one the planner will often push down into WHERE for you, but writing it there yourself is what lets an index serve it.

COUNT(DISTINCT x) is the expensive variant: the accumulator must remember every value seen per group, so it is a hash set per group or a sort with duplicate elimination, and on a wide table with many groups it is the aggregate that turns a one-second report into a minute. Approximate alternatives exist for the cases where "about 1.2 million" is enough: PostgreSQL's hll extension, MySQL's APPROX_COUNT_DISTINCT in some forks, and pre-aggregated summary tables everywhere.

#Walkthrough: the KPI that was NULL every Monday

A weekly digest emailed an average order value per region, and one region's number was blank on some weeks and not others.

SELECT r.name,
       SUM(o.total) / COUNT(o.id) AS avg_order_value
FROM regions r
LEFT JOIN orders o ON o.region_id = r.id AND o.placed_at >= now() - interval '7 days'
GROUP BY r.name;

1. A region with no orders in the week produced one padded row from the LEFT JOIN: o.total NULL, o.id NULL. SUM over a group of one NULL is NULL and COUNT(o.id) is 0. NULL / 0 is NULL, not a division-by-zero error, so the row rendered blank rather than failing.
2. The following week the same region had one order, and the query returned a number, so the blank looked like a data problem, not a query problem. It was blamed on the ETL for a month.
3. Someone "fixed" it with COUNT(*) in the denominator. For a region with real orders nothing changed, since it has no padded row; for the empty region the expression became NULL / 1, still NULL. The blank stayed, and the ticket was closed as fixed.
4. The correct shape: COALESCE(SUM(o.total), 0) for the total, COUNT(o.id) for the count, and AVG(o.total) for the average, which ignores NULLs and returns NULL for an empty group, which the report then renders as "no orders" rather than blank. NULLIF(COUNT(o.id), 0) is the guard when a ratio must be computed by hand.
5. The test added was a region fixture with zero orders and an assertion on the rendered text, because the number was never going to fail; only its meaning was wrong.

Aggregates over an empty set are NULL, COUNT is 0, and arithmetic with NULL is NULL. Every ratio in a report needs to say what it means when the denominator is empty.

#Try it yourself

SELECT customer_id, COUNT(*) FROM orders
GROUP BY customer_id
HAVING MAX(status) = 'PAID' AND MIN(placed_at) >= '2026-01-01';

#Misconceptions

• "GROUP BY runs before the joins." Joins and WHERE come first; the accumulator sees already-multiplied rows, which is why fan-out inflates sums.
• "SUM of nothing is zero." It is NULL, COUNT is zero, and NULL arithmetic is NULL. Guard ratios with COALESCE and NULLIF.
• "HAVING is just WHERE for groups, interchangeable when possible." A condition that can be WHERE should be: it filters before aggregation and can use an index.
• "COUNT(DISTINCT) is a cheap variant of COUNT." It keeps every value per group; on a large table it dominates the query.
• "MySQL's old lax GROUP BY was a feature." It returned an arbitrary value for an ungrouped column, silently; ONLY_FULL_GROUP_BY exists to stop that.

#Going deeper

• PostgreSQL manual, "Aggregate Functions", including the FILTER clause, ordered-set aggregates, and GROUPING SETS.
• PostgreSQL 13 release notes, "Allow hash aggregation to use disk storage", and the enable_hashagg planner setting.
• MySQL reference, "GROUP BY Optimization" and "MySQL Handling of GROUP BY" (ONLY_FULL_GROUP_BY).
• The hll and datasketches PostgreSQL extensions, for approximate distinct counts.
• Anthony Molinaro, SQL Cookbook, chapter on reporting and warehousing.