Phase 2 of 8 · 5 lessons
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.
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 order
-
2.1
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. -
2.2
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.
-
2.3
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. -
2.4
Conditional aggregation: counting several things at once
Putting CASE inside an aggregate —
SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END)— counts or sums only the rows that match, so one query can produce several different totals, or turn rows into columns (a pivot). -
2.5
The order a query really runs in
A query is written SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, but the database works it out in a different order — FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT — and that order explains most "why doesn't this work?" errors.