Unit 2: Summarising data: aggregates, GROUP BY and HAVING
Turn many rows into answers — totals, averages, counts per group — and know exactly which rows each number is built from.
The big picture
Questions like "how many orders per customer?" or "average salary per department?" are the heart of analytics and of SQL interviews. They all use the same three tools: an aggregate function, GROUP BY to make the groups, and HAVING to filter them.
Lessons in this unit
- Aggregate functions: COUNT, SUM, AVG, MIN and MAX: An aggregate function turns many rows into one value — COUNT counts, SUM adds, AVG averages, MIN and MAX find the smallest and largest — and every one except `COUNT(*)` skips NULLs.
- GROUP BY: one result row per group: GROUP BY splits the rows into groups that share the same value in the listed columns, and the aggregates then return one row per group.
- HAVING: filtering groups, and how it differs from WHERE: HAVING filters groups after GROUP BY, using aggregates such as `COUNT(*) > 1`, while WHERE filters single rows before any grouping happens.
- Conditional aggregation: counting several things at once: Conditional aggregation puts CASE inside an aggregate, such as `SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END)`, so that it counts or sums only the rows that match. One query can then produce several different totals, or turn rows into columns (a pivot).
- The order a query really runs in: You write a query as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. The database works it out in a different order: FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. That order explains most "why doesn't this work?" errors.