GROUP BY and Aggregates

Grouping collapses rows. Every bug in this area comes from forgetting how many rows went into each group.

What you may select

SELECT country, name, count(*)
FROM customers
GROUP BY country;

$ duckdb interview.duckdb < bad.sql
Binder Error: column "name" must appear in the GROUP BY clause or must be part of an aggregate function.
Either add it to the GROUP BY list, or use "ANY_VALUE(name)" if the exact value of "name" is not important.

Two customers are in the UK. There is no single name for that group, so the engine refuses.

The error message names the three legitimate resolutions:

SELECT country,
       count(*)                              AS n,
       string_agg(name, ', ' ORDER BY name)  AS members,
       min(name)                             AS first_alphabetically,
       any_value(name)                       AS arbitrary
FROM customers
GROUP BY country
ORDER BY country NULLS LAST;

┌─────────┬───────┬──────────┬──────────────────────┬───────────┐
│ country │   n   │ members  │ first_alphabetically │ arbitrary │
├─────────┼───────┼──────────┼──────────────────────┼───────────┤
│ India   │     1 │ Di       │ Di                   │ Di        │
│ UK      │     2 │ Ana, Cy  │ Ana                  │ Ana       │
│ US      │     2 │ Bo, Eve  │ Bo                   │ Bo        │
│ NULL    │     1 │ Fay      │ Fay                  │ Fay       │
└─────────┴───────┴──────────┴──────────────────────┴───────────┘

NULL is a group. Fay’s NULL country forms its own row rather than being dropped — the opposite of how NULL behaves in joins and comparisons, and worth saying out loud because the inconsistency catches people.

ORDER BY country NULLS LAST is explicit about where it lands. Defaults differ: PostgreSQL puts NULLs last ascending, MySQL puts them first.

MySQL before 5.7 accepted the original query and returned an arbitrary name. Code written against that behaviour breaks when it moves — which is why any_value() exists: it makes “I genuinely don’t care which” visible in the query.

WHERE filters rows, HAVING filters groups

SELECT c.country,
       count(*)      AS shipped_orders,
       sum(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'shipped'          -- drops rows before grouping
GROUP BY c.country
HAVING sum(o.amount) > 100          -- drops groups after aggregating
ORDER BY revenue DESC;

┌─────────┬────────────────┬─────────┐
│ country │ shipped_orders │ revenue │
├─────────┼────────────────┼─────────┤
│ UK      │              3 │  400.50 │
│ US      │              1 │  310.00 │
│ India   │              1 │  150.00 │
└─────────┴────────────────┴─────────┘

Both clauses are doing work that only they can do:

• WHERE o.status = 'shipped' removes the cancelled and pending orders. It cannot go in HAVING as written, because by then the individual statuses are gone.
• HAVING sum(o.amount) > 100 needs the total, which does not exist until the group is formed.

The order of execution from the intro lesson is what makes this predictable: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.

Conditional aggregation

The question “give me total, shipped, and cancelled counts per country” invites three queries. It should be one.

SELECT c.country,
       count(*)                                        AS all_orders,
       count(*) FILTER (WHERE o.status = 'shipped')    AS shipped,
       count(*) FILTER (WHERE o.status = 'cancelled')  AS cancelled,
       sum(o.amount) FILTER (WHERE o.status = 'shipped') AS shipped_revenue,
       round(100.0 * count(*) FILTER (WHERE o.status = 'shipped') / count(*), 1) AS pct_shipped
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.country
ORDER BY all_orders DESC, c.country;

┌─────────┬────────────┬─────────┬───────────┬─────────────────┬─────────────┐
│ country │ all_orders │ shipped │ cancelled │ shipped_revenue │ pct_shipped │
├─────────┼────────────┼─────────┼───────────┼─────────────────┼─────────────┤
│ UK      │          4 │       3 │         1 │          400.50 │        75.0 │
│ US      │          2 │       1 │         0 │          310.00 │        50.0 │
│ India   │          1 │       1 │         0 │          150.00 │       100.0 │
└─────────┴────────────┴─────────┴───────────┴─────────────────┴─────────────┘

FILTER (WHERE ...) is standard SQL and works in PostgreSQL, DuckDB, and SQLite. The portable form works everywhere:

SELECT c.country,
       sum(CASE WHEN o.status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
       sum(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.country ORDER BY c.country;

┌─────────┬─────────┬───────────┐
│ country │ shipped │ cancelled │
├─────────┼─────────┼───────────┤
│ India   │       1 │         0 │
│ UK      │       3 │         1 │
│ US      │       1 │         0 │
└─────────┴─────────┴───────────┘

Note 100.0 * rather than 100 * in the percentage. Integer division is a classic silent wrong answer:

SELECT 3 / 4 AS integer_division, 3.0 / 4 AS decimal_division;

┌──────────────────┬──────────────────┐
│ integer_division │ decimal_division │
├──────────────────┼──────────────────┤
│             0.75 │             0.75 │
└──────────────────┴──────────────────┘

DuckDB and PostgreSQL differ here: PostgreSQL returns 0 for 3 / 4 on integers, DuckDB returns 0.75. So the same query gives different answers on different engines, which is exactly why you write 100.0 * regardless of which one you are on. Say the dialect assumption out loud.

Fan-out: the join that doubles your total

-- correct: one row per order
SELECT sum(amount) AS revenue FROM orders WHERE status = 'shipped';

┌─────────┐
│ revenue │
├─────────┤
│  935.50 │
└─────────┘

-- "let me also get the item count" — and the total is now wrong
SELECT sum(o.amount) AS revenue, count(*) AS rows_scanned
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'shipped';

┌──────────┬──────────────┐
│ revenue  │ rows_scanned │
├──────────┼──────────────┤
│  1290.50 │            7 │
└──────────┴──────────────┘

Revenue moved from 935.50 to 1290.50 by adding a join that was supposed to be informational. Two errors compounded, and both are invisible:

• Fan-out. Orders 101 and 103 have two line items each, so their amounts are summed twice.
• A silently dropped row. Order 108 has no line items, and the inner join removed it — so its 75.00 vanished at the same time as the others doubled.

Nothing errored. The number is plausible. This is the most expensive bug in the subject because it survives review.

Two correct shapes:

-- 1. aggregate the child table first, then join one-to-one
WITH item_counts AS (
    SELECT order_id, sum(quantity) AS units
    FROM order_items GROUP BY order_id
)
SELECT sum(o.amount) AS revenue, sum(coalesce(ic.units, 0)) AS units
FROM orders o
LEFT JOIN item_counts ic ON ic.order_id = o.order_id
WHERE o.status = 'shipped';

-- 2. aggregate a distinct key
SELECT sum(DISTINCT o.amount) AS risky, count(DISTINCT o.order_id) AS order_count
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'shipped';

┌─────────┬───────┐        ┌────────┬─────────────┐
│ revenue │ units │        │ risky  │ order_count │
├─────────┼───────┤        ├────────┼─────────────┤
│  935.50 │    14 │        │ 860.50 │           5 │
└─────────┴───────┘        └────────┴─────────────┘

The first is right: 935.50 matches the un-joined total, and the LEFT JOIN keeps order 108 at zero units instead of dropping it.

The second is wrong on both counts, and deliberately so. sum(DISTINCT amount) returns 860.50 — it fixes the double-counting but still misses order 108, and it would silently merge two genuinely different orders that happened to share an amount. count(DISTINCT id) is safe because ids are unique; sum(DISTINCT value) almost never is.

“Aggregate before joining. If I join a one-per-order table to a many-per-order table and then SUM, each order’s amount is counted once per line item. count(DISTINCT id) is safe; sum(DISTINCT amount) silently merges equal values from different rows.”

GROUPING SETS, ROLLUP, and totals

SELECT coalesce(c.country, 'ALL') AS country,
       coalesce(o.status, 'ALL')  AS status,
       count(*)                   AS n,
       sum(o.amount)              AS revenue
FROM customers c JOIN orders o ON o.customer_id = c.customer_id
GROUP BY ROLLUP (c.country, o.status)
ORDER BY country, status;

┌─────────┬───────────┬───────┬─────────┐
│ country │  status   │   n   │ revenue │
├─────────┼───────────┼───────┼─────────┤
│ ALL     │ ALL       │     7 │ 1005.49 │
│ India   │ ALL       │     1 │  150.00 │
│ India   │ shipped   │     1 │  150.00 │
│ UK      │ ALL       │     4 │  445.50 │
│ UK      │ cancelled │     1 │   45.00 │
│ UK      │ shipped   │     3 │  400.50 │
│ US      │ ALL       │     2 │  409.99 │
│ US      │ pending   │     1 │   99.99 │
│ US      │ shipped   │     1 │  310.00 │
└─────────┴───────────┴───────┴─────────┘

ROLLUP produces the subtotals and grand total in one pass, replacing a UNION ALL of three queries. It is a good thing to reach for when the question says “with totals” — and a good thing to name even if you write the UNION version, because it shows you know the cost difference.

The coalesce wrappers are needed because ROLLUP marks the aggregated levels with NULL, which is indistinguishable from Fay’s real NULL country without them. GROUPING(country) returns 1 for a rollup NULL and 0 for a data NULL when you need to tell them apart.

Recognising it

QUESTION                                        SHAPE
"per X, count / sum / average"                  GROUP BY X
"only groups where the total is over N"         HAVING
"only rows matching a condition"                WHERE
"count of A and count of B side by side"        FILTER (WHERE ...) or SUM(CASE WHEN)
"percentage of the group"                       100.0 * part / total — force the decimal
"with subtotals and a grand total"              ROLLUP or GROUPING SETS
joining a one-per-X to a many-per-X, then SUM   fan-out — aggregate first
"the name of the customer with the most ..."    not GROUP BY — a window function (next lesson)

Practice

Binder Error: column "name" must appear in the GROUP BY clause ...

before: 935.50      after joining order_items: 1290.50

PostgreSQL: 3 / 4 = 0      DuckDB: 3 / 4 = 0.75

NULL forms its own group — unlike in joins, where NULL matches nothing.

Next: window functions — the ranking family, running totals, and frames.