3.2Aggregates, GROUP BY and HAVING
The idea
Aggregates (COUNT, SUM, AVG, MIN, MAX) summarise rows. GROUP BY makes one group per value; HAVING filters those groups.
Think of it like this: Sorting class marks by division, then keeping only divisions whose average is high.
Why it exists
Aggregate and grouping questions are the most frequent SQL output questions.
How it works
- WHERE removes rows before grouping.
- GROUP BY forms groups; aggregates are computed per group.
- HAVING removes groups after aggregation and can use aggregates.
- COUNT(*) counts rows, COUNT(col) skips NULLs; other aggregates also skip NULLs.
Worked example: SELECT dept, COUNT(*) FROM Emp WHERE sal > 35 GROUP BY dept HAVING COUNT(*) >= 2
WHERE drops low salaries and NULL, then groups with fewer than 2 rows are dropped: CSE and ECE survive.
Now play with it
- →Switch to the NULL query to compare COUNT(*) with COUNT(sal).
- →Run the aggregate without GROUP BY: one group.
| eid | dept | sal |
|---|---|---|
| 1 | CSE | 50 |
| 2 | CSE | 70 |
| 3 | ECE | 40 |
| 4 | ECE | 60 |
| 5 | ECE | 80 |
| 6 | ME | 30 |
| 7 | ME | NULL |
| dept | COUNT(*) | AVG(sal) |
|---|---|---|
| CSE | 2 | 60 |
| ECE | 3 | 60 |
FROM → WHERE (filters rows) → GROUP BY → HAVING (filters groups) → SELECT
| Clause | Filters | May use aggregates? |
|---|---|---|
| WHERE | rows, before grouping | no |
| GROUP BY | forms groups | n/a |
| HAVING | groups, after grouping | yes |
| SELECT | chooses output columns | yes |
Check yourself
Which clause can contain an aggregate function like COUNT(*) in its condition?
Exam notes (the dense version)
COUNT(*) counts rows; COUNT(col), SUM, AVG, MIN, MAX ignore NULLs. WHERE filters rows before grouping; HAVING filters groups after and may use aggregates. Every non-aggregated column in SELECT must appear in GROUP BY. Without GROUP BY the whole table is one group; an aggregate over an empty set gives 0 for COUNT and NULL for the others.
Previous year questions: SQL (13)
PYQs are grouped by topic, so this is the full set for SQL; try the ones that match this subtopic. Open all 13 on the topic page.