~/learn/SQL · Aggregation
Aggregation and GROUP BY
Collapse many rows into one number per group.
why this matters
Managers ask for totals and averages: revenue per country, orders per month, top spenders. Aggregation turns thousands of rows into those answers.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
Mental model
8 product rows -> GROUP BY category buckets them: Hardware(3) Audio(2) Furniture(2) Video(1) -> COUNT(*) turns each bucket into one row. You get one output row per distinct group, not per original row.
Aggregate functions reduce a set of rows to a single value: COUNT, SUM, AVG, MIN, MAX. Used alone they summarise the whole table.
GROUP BY splits the table into buckets first, then applies the aggregate to each bucket. Every column in the SELECT list must either be grouped or aggregated.
WHERE filters rows before grouping; HAVING filters the groups afterwards. If your condition mentions an aggregate, it belongs in HAVING.
Common gotcha
WHERE COUNT(*) >= 3 is an error: WHERE runs before the groups exist. Any condition on a count, sum or average belongs in HAVING.
Pro tip
COUNT(*) counts rows, COUNT(column) skips NULLs in that column. When those two numbers differ, you have found missing data.
The GROUP BY rule
Every column in SELECT must either be inside an aggregate (SUM, COUNT…) or listed in GROUP BY. If SQL complains, check this first.
key takeaways
- Aggregates collapse many rows into one number.
- GROUP BY makes one bucket per value.
- HAVING filters buckets; WHERE filters rows.
check your understanding
You want countries with more than 2 customers. Where does the COUNT condition go?
free practice
Build confidence with a question you can solve without Pro.