← QCE Digital Solutions Unit 3

SQL HAVING

Filters groups after GROUP BY using a condition on an aggregate, e.g. HAVING COUNT(*) > 2. WHERE filters rows before grouping. HAVING comes after GROUP BY.

Part of: QCE Digital Solutions Unit 3 — Innovative Digital Solutions — Module 33

Understanding SQL HAVING in QCE Digital Solutions Unit 3

What it does: The WHERE condition checks each row's yearLevel and removes rows before GROUP BY builds the groups. The correct order is WHERE, GROUP BY, HAVING, then ORDER BY, so HAVING sits straight after GROUP BY. HAVING applies a condition to the aggregate COUNT(*) after the groups have been formed, so small groups are removed. HAVING can test an aggregate function such as AVG, which WHERE is not able to do.

Common mistakes: The grouping clause is GROUP BY, not GROUP. The line should be GROUP BY house, and HAVING then filters those groups. HAVING filters groups, so the groups must be created first with GROUP BY. The line should be GROUP BY house, not ORDER BY.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add SQL HAVING to a class →

This module's key terms are also in a printable crossword (Modules 251–337) and word search (Modules 194–337).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
Which clause filters groups after GROUP BY using an aggregate condition?
HAVING COUNT(*) > 2WHERE COUNT(*) > 2
Matches
SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 2 GROUP BY house;
↓
WHERE cannot use an aggregate like COUNT(*) because it filters rows before groups exist. The condition should be in HAVING after GROUP BY.
Applying
Meanings
Term
HAVING
Meaning
Filters groups after GROUP BY using a condition on an aggregate, e.g. HAVING COUNT(*) > 2. WHERE filters rows before grouping. HAVING comes after GROUP BY.
Practice
SELECT subjectId, COUNT(*)
Testing
Gaps
COUNT(*) > 2;
Check Answer
Spots
1SELECT house FROM students
2GROUP house

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Anglerfish' badge — Deep Diver, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which clause filters groups after GROUP BY using an aggregate condition?
A. WHERE COUNT(*) > 2
B. HAVING COUNT(*) > 2 ✓
HAVING is used to filter groups by an aggregate such as COUNT(*). WHERE runs before grouping, so it cannot use aggregate functions.
2. Which query correctly lists houses with more than 2 students?
A. SELECT house FROM students GROUP BY house HAVING COUNT(*) > 2; ✓
B. SELECT house FROM students GROUP BY house WHERE COUNT(*) > 2;
The aggregate condition must go in HAVING after GROUP BY. WHERE cannot test COUNT(*) and causes an error.
3. When does WHERE filter data compared with HAVING?
A. WHERE filters rows before grouping; HAVING filters groups after grouping ✓
B. WHERE filters groups after grouping; HAVING filters rows before grouping
Order of work: WHERE removes rows first, then GROUP BY makes groups, then HAVING removes groups.
4. Which is the correct clause order?
A. SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY ✓
B. SELECT, FROM, HAVING, GROUP BY, WHERE, ORDER BY
HAVING must come after GROUP BY. WHERE comes before GROUP BY. ORDER BY comes last.
5. Which condition belongs in WHERE, not HAVING?
A. AVG(points) > 100
B. yearLevel = 8 ✓
yearLevel = 8 tests each individual row, so it goes in WHERE. AVG(points) > 100 tests a group, so it goes in HAVING.

Match the Pairs

GROUP BY house HAVING COUNT(*) > 2 → Groups students by house, then keeps only the houses that have more than 2 students.
HAVING AVG(mark) >= 75 → Keeps only the groups whose average mark is 75 or higher.
WHERE filters individual rows before grouping, so it can remove Year 7 students before they are counted. → SELECT house, COUNT(*) FROM students WHERE yearLevel = 7 GROUP BY house;
Clause order: HAVING comes after GROUP BY and before ORDER BY. → SELECT house, SUM(points) FROM students GROUP BY house HAVING SUM(points) > 200 ORDER BY house;
SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 2 GROUP BY house; → WHERE cannot use an aggregate like COUNT(*) because it filters rows before groups exist. The condition should be in HAVING after GROUP BY.
SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 2 GROUP BY house; → HAVING is placed before GROUP BY. It must come after GROUP BY, as it filters the groups that GROUP BY creates.

Key Term

HAVING
Filters groups after GROUP BY using a condition on an aggregate, e.g. HAVING COUNT(*) > 2. WHERE filters rows before grouping. HAVING comes after GROUP BY.

Fill in the Blanks

1. SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 2;
Hint: Type the keyword that filters groups after they are formed, using a condition on an aggregate.
2. SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 2;
Hint: Type the aggregate function that counts the rows in each group.
3. SELECT house, COUNT(*) FROM students WHERE yearLevel = 8 GROUP BY house;
Hint: Type the keyword that filters individual rows before they are grouped.
4. SELECT category, COUNT(*) FROM products GROUP BY category HAVING COUNT(*) > 2;
Hint: Type the clause that must come before HAVING and builds the groups.
5. SELECT subjectId, AVG(mark) FROM results GROUP BY subjectId HAVING AVG(mark) > 70;
Hint: Type the comparison operator that keeps only groups whose average is above the limit.

Spot the Error

Question 1
A SELECT house FROM students
B GROUP house
C HAVING COUNT(*) > 2;
Line B — The grouping clause is GROUP BY, not GROUP. The line should be GROUP BY house, and HAVING then filters those groups.
Question 2
A SELECT house, AVG(points)
B FROM students
C ORDER BY house
D HAVING AVG(points) > 100;
Line C — HAVING filters groups, so the groups must be created first with GROUP BY. The line should be GROUP BY house, not ORDER BY.
Question 3
A SELECT house, COUNT(*)
B FROM students
C GROUP BY house
D HAVING COUNT > 2;
Line D — COUNT is a function, so it needs brackets and an argument. It should be HAVING COUNT(*) > 2; to count the rows in each group.
Question 4
A SELECT subjectId AVG(mark)
B FROM results
C GROUP BY subjectId
D HAVING AVG(mark) > 70;
Line A — Items in the SELECT list must be separated by a comma. It should be SELECT subjectId, AVG(mark).
Question 5
A SELECT category, COUNT(*)
B FROM products
C GROUP BY category
D WHERE COUNT(*) >= 2
E ORDER BY COUNT(*) DESC;
Line D — WHERE filters rows before grouping, so it cannot use an aggregate like COUNT(*). A condition on a group count belongs in HAVING, after GROUP BY.

Student with a class code? Join your class →