GROUP BY, HAVING and Conditional Aggregation

Description

So far every query has looked at rows one at a time. Reports rarely want that: they want one line per something - revenue per country, orders per customer, a cancellation rate per month. That's what GROUP BY does. It sorts rows into groups and boils each group down to a few numbers with aggregates such as COUNT, SUM and AVG.

It's easy to start with, and it's also where reporting numbers quietly go wrong. Three classic examples from a finance dashboard's first week:

• Someone adds "only countries over $100" as WHERE SUM(total) > 100, and Postgres refuses to run it.
• Revenue jumps 40% overnight after a join to order_items - no new sales, the same orders just counted once per line item.
• The average order value creeps up because a new column is NULL for half the rows, and AVG skips NULLs instead of treating them as zero.

The last two run without any error. By the end of this lesson you'll spot all three on sight. We'll go from simple to more advanced:

1. One row per group, and what you're allowed to SELECT after grouping.
2. WHERE versus HAVING - filtering rows versus filtering groups.
3. How aggregates treat NULL.
4. Several counts in one pass with FILTER, and rates that don't round to zero.
5. Fan-out: why a join can inflate your totals.
6. Subtotals and grand totals with ROLLUP.

All examples below use one small dataset: five customers (with a country), eight orders (status, total; order 108 is a guest checkout with no customer), and order_items (quantity, unit_price per product line). Every result shown was produced by PostgreSQL 18.

One row per group

GROUP BY collapses all rows that share the grouping columns into one output row. After it runs, each row stands for a group, so everything in SELECT must be one value per group: a grouping column, or an aggregate (COUNT, SUM, AVG, MIN, MAX, string_agg, ...).

SELECT c.country, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.country
ORDER BY revenue DESC, c.country;

Selecting o.total here would be an error - which of a country's totals would it show? One exception trips people up in reviews: Postgres lets you select any column of a table whose primary key you grouped by (GROUP BY c.id makes c.name legal), because it's functionally dependent - one id, one name. Grouping by c.id, c.name is the portable way to say the same thing.

Note the ORDER BY revenue DESC, c.country: two countries tie at 100.00, and without the second key their order is whatever the plan happens to produce.

WHERE filters rows, HAVING filters groups

The logical order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE runs before any group exists, so it can't use an aggregate - that is why WHERE SUM(total) > 100 is an error. A condition on the group goes in HAVING:

SELECT c.country, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY c.country
HAVING SUM(o.total) >= 100
ORDER BY c.country;

Put each condition where it belongs: a condition on rows (status = 'paid') in WHERE, so those rows never reach the grouping; a condition on groups (SUM(...) >= 100) in HAVING. HAVING status = 'paid' would be an error - status isn't a single value per group - and a row condition moved into HAVING where it's legal (a grouping column) still means grouping rows you're about to throw away.

Aggregates and NULL

Aggregates skip NULLs, and that has three consequences worth memorizing:

• COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL. After a LEFT JOIN, a customer with no match still gets one row with NULLs - COUNT(*) says 1, COUNT(o.id) says 0.
• SUM, AVG, MIN, MAX over no values (or only NULLs) return NULL, not 0. Wrap in COALESCE(SUM(x), 0) when the report needs a zero.
• AVG(x) divides by the number of non-NULL values. If NULL means "unknown", that's right; if it means "zero", say so with AVG(COALESCE(x, 0)).

SELECT c.name,
       COUNT(*)    AS count_star,
       COUNT(o.id) AS paid_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;

Sofia and Emma have no paid orders, yet COUNT(*) says 1 - it's counting the NULL-extended row the outer join created. Note too that the status condition sits in the join's ON: in WHERE it would delete those NULL rows and quietly turn the outer join back into an inner one.

Conditional aggregation

Often you want several counts per group in one pass - paid versus lost orders per customer. A WHERE can't do it (it would remove the rows the other count needs). Attach the condition to the aggregate instead:

SELECT c.name,
       COUNT(*) FILTER (WHERE o.status = 'paid')                      AS paid,
       COUNT(*) FILTER (WHERE o.status IN ('cancelled', 'refunded'))  AS lost,
       SUM(o.total) FILTER (WHERE o.status = 'paid')                  AS paid_revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

FILTER (WHERE ...) is standard SQL and supported by Postgres; the portable spelling in older engines is SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END). Two traps: forget the ELSE 0 and a group with no matches sums to NULL; and COUNT(*) FILTER of nothing is 0 while SUM(...) FILTER of nothing is NULL (Sofia's paid_revenue).

Rates follow the same pattern - and bring an integer trap: COUNT(...) / COUNT(*) divides two integers and truncates, so 1 of 3 is 0. Multiply by 100.0 (a numeric) first: ROUND(100.0 * COUNT(*) FILTER (WHERE ...) / COUNT(*), 1).

Fan-out: when joining multiplies your numbers

A join to a one-to-many table repeats the "one" side once per match. Aggregating a column of the "one" side after that join counts it again for every child row:

SELECT COUNT(*) AS joined_rows, SUM(o.total) AS summed_totals
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'paid';

The five paid orders with items are worth 230.00, but orders 101 and 106 have two lines each, so their totals were added twice. Fix it by aggregating at the level you joined to (sum the items), counting the parent with COUNT(DISTINCT ...), or aggregating each child table in its own subquery before joining:

SELECT SUM(i.quantity * i.unit_price) AS revenue, COUNT(DISTINCT o.id) AS orders
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'paid';

Fan-out is the most expensive bug in reporting SQL because the numbers are plausible - just too big - and a single-item test dataset hides it completely. Joining two child tables of the same parent (items and payments) multiplies them together; aggregate each one first.

Subtotals with ROLLUP

GROUP BY ROLLUP (a, b) produces the groups for (a, b), then subtotals for each a, then a grand total - the rolled-up columns are NULL in the subtotal rows. GROUPING(col) returns 1 on those rows, which tells a subtotal's NULL apart from a real NULL in the data:

SELECT COALESCE(c.country, 'All countries') AS country, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
GROUP BY ROLLUP (c.country)
ORDER BY GROUPING(c.country), c.country;

GROUPING SETS ((a, b), (a), ()) lists exactly the groupings you want, and CUBE (a, b) produces every combination. Order by GROUPING(...) to put the totals last.

Which tool for which question?

Where it breaks

• Aggregates in WHERE - an error. Move the condition to HAVING.
• Status or date filters in HAVING instead of WHERE - legal only for grouped columns, and it groups rows you then discard.
• COUNT(*) after an outer join - counts customers with no match as 1. Count a column from the joined table.
• SUM of nothing is NULL - reports show blanks where the business expects 0. COALESCE.
• Integer division in rates - 7 / 9 is 0. Use 100.0 *, or cast.
• Fan-out - joining a child table multiplies the parent's numbers. Aggregate at one level per query, or pre-aggregate.
• Ties in ORDER BY - two equal revenues come back in an order that can change between runs; add a tiebreaker.

Tradeoffs

• FILTER is clearer than CASE inside an aggregate, and says what you mean; CASE is the version that runs everywhere.
• COUNT(DISTINCT x) fixes a fan-out count, but on large joins it's expensive (it has to deduplicate); pre-aggregating each child table and joining the results is usually faster and makes the grain obvious.
• ROLLUP gives totals in one query and one scan; a UNION ALL of separate queries is easier to read but scans again for every level.

Going further. The deep dive (next tab) covers how the database actually computes a GROUP BY - hashing or sorting, and what happens when the groups don't fit in memory - why COUNT(DISTINCT) is the expensive one, what aggregates do to number types, and how dashboards aggregate billions of rows without scanning them every time.

Check yourself

1. Why does WHERE COUNT(*) > 5 fail, and where does the condition go?

2. After customers LEFT JOIN orders, a customer has no orders. What do COUNT(*) and COUNT(o.id) return for them?

3. Revenue went up after you joined order_items to add a "units sold" column. What happened, and name two fixes.

Key takeaways

• After GROUP BY, every selected value must be one per group: a grouping column or an aggregate.
• WHERE filters rows before grouping; HAVING filters groups after it.
• Aggregates skip NULLs: COUNT(col) ≠ COUNT(*), SUM of nothing is NULL, AVG ignores NULLs.
• COUNT(*) FILTER (WHERE ...) computes several conditional aggregates in one pass.
• Joining a one-to-many table multiplies the parent's rows - aggregate at one grain, or pre-aggregate.
• ROLLUP / GROUPING SETS add subtotals; GROUPING() tells a subtotal apart from real NULLs.