Grouping and Aggregation

Aggregation changes grain. A query that groups by region returns one row per region, not one row per customer or order. Every selected expression must either define that grouping grain or summarize rows within it.

Prerequisites

Understand join fanout and the grains of customers and orders.

Learning goals

• State the output grain of a GROUP BY query.
• Choose between WHERE, aggregate FILTER, and HAVING.
• Handle NULL values and empty groups deliberately.
• Avoid summing a measure after duplicating it in a join.

Grouping grain and filters

WHERE removes input rows before groups form. HAVING removes whole groups after aggregation. Standard SQL's aggregate FILTER (WHERE ...) can feed different subsets to different aggregates without discarding the row from every measure.

COUNT(*) counts rows. COUNT(expression) counts rows where the expression is not NULL. Most numeric aggregates ignore NULL; if all inputs are missing, SUM and AVG return NULL, not zero.

Worked example

This portable Standard SQL summary has one row per known region. North contains Ada's two orders and Cy's one order; South contains Ben's two rows, including the order with a missing amount.

SELECT c.region,
       COUNT(*) AS order_rows,
       SUM(o.amount) AS known_amount
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.region
ORDER BY c.region;

region	order_rows	known_amount
North	3	180.00
South	2	25.00

HAVING applies after groups exist.

SELECT c.region, SUM(o.amount) AS known_amount
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.region
HAVING SUM(o.amount) > 100
ORDER BY c.region;

region	known_amount
North	180.00

DuckDB-specific: GROUP BY ALL groups by every selected non-aggregate expression. It can reduce drift during exploration, but explicit columns communicate portable contracts more clearly.

Limitations and common mistakes

• Selecting a column that is neither grouped nor aggregated is ambiguous and rejected by conforming systems.
• AVG averages rows, not entities. If customers have different numbers of orders, average order value and average customer value answer different questions.
• Joining order-level amounts to item rows before summing overstates revenue.
• Converting every missing aggregate to zero can erase the difference between “no observation” and “observed zero.”

Practice

Produce one row per status with total row count, count of known amounts, and known total amount. Explain the cancelled group's three outputs.

Next steps

Use subqueries and common table expressions to name intermediate grains and break larger transformations into inspectable stages.

Primary sources

Checked 2026-10-14: DuckDB GROUP BY, HAVING, aggregate functions, and the Standard SQL FILTER clause.