← QCE Digital Solutions Unit 2

SQL HAVING

Filters groups after GROUP BY using an aggregate condition, such as HAVING COUNT(*) > 3. WHERE filters rows before grouping.

Part of: QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 36

Understanding SQL HAVING in QCE Digital Solutions Unit 2

What it does: WHERE removes rows that are not in Year 7 before GROUP BY forms the groups, so it cannot use aggregates. The AVG(mark) is only known once each subject group exists, so the condition must go in HAVING after GROUP BY.

Common mistakes: GROUP BY needs the word BY. The line should be FROM students GROUP BY house so the rows are grouped before HAVING filters the groups. WHERE filters single rows before grouping, so it cannot use an aggregate like AVG(). To filter groups by their average, use HAVING AVG(points) > 100;.

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 305–336) and word search (Modules 234–409).

Take this worksheet further — free with Cloodit

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

Learning
Duo
Which query lists houses that have more than 2 students?
SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 2;SELECT house, COUNT(*) FROM students GROUP BY house WHERE COUNT(*) > 2;
Matches
SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 3 GROUP BY house;
↓
An aggregate such as COUNT(*) is used in WHERE. WHERE runs before grouping, so move the condition to HAVING after GROUP BY.
Applying
Meanings
Term
HAVING
Meaning
Filters groups after GROUP BY using an aggregate condition, such as HAVING COUNT(*) > 3. WHERE filters rows before grouping.
Practice
SELECT studentId, AVG(mark)
Testing
Gaps
COUNT(*) > 2;
Check Answer
Spots
1SELECT house, COUNT(*)
2FROM students GROUP 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 query lists houses that have more than 2 students?
A. SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 2; ✓
B. SELECT house, COUNT(*) FROM students GROUP BY house WHERE COUNT(*) > 2;
WHERE cannot use an aggregate like COUNT(*). HAVING filters the groups after GROUP BY.
2. HAVING filters groups. WHERE filters rows. Which statement is true?
A. WHERE filters groups after GROUP BY
B. WHERE filters rows before they are grouped ✓
WHERE runs first on individual rows. HAVING runs after GROUP BY on the groups.
3. Which clause order is correct?
A. GROUP BY house HAVING COUNT(*) > 2 ✓
B. HAVING COUNT(*) > 2 GROUP BY house
HAVING comes after GROUP BY because it filters the groups that GROUP BY creates.
4. You want only subjects where the average mark is above 70. Which condition is correct?
A. WHERE AVG(mark) > 70
B. HAVING AVG(mark) > 70 ✓
AVG is an aggregate calculated per group, so the condition must go in HAVING.
5. You want to ignore students in Year 7 before counting students per house. Which is the best choice?
A. WHERE yearLevel <> 7 ✓
B. HAVING yearLevel <> 7
yearLevel is a row value, not an aggregate, so WHERE removes those rows before grouping. HAVING would fail here because yearLevel is not in the GROUP BY.

Match the Pairs

GROUP BY house HAVING COUNT(*) > 3 → Keeps only the houses that have more than 3 students
GROUP BY subjectId HAVING AVG(mark) >= 70 → Keeps only the subjects whose average mark is 70 or more
WHERE filters individual rows before grouping, so it is used for plain column conditions → SELECT house, COUNT(*) FROM students WHERE yearLevel = 7 GROUP BY house;
HAVING filters whole groups after GROUP BY, so it can use an aggregate such as AVG → SELECT subjectId, AVG(mark) FROM results GROUP BY subjectId HAVING AVG(mark) > 75;
SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 3 GROUP BY house; → An aggregate such as COUNT(*) is used in WHERE. WHERE runs before grouping, so move the condition to HAVING after GROUP BY.
SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 3 GROUP BY house; → HAVING is placed before GROUP BY. The clause order is wrong: HAVING must come after GROUP BY.

Key Term

HAVING
Filters groups after GROUP BY using an aggregate condition, such as HAVING COUNT(*) > 3. WHERE filters rows before grouping.

Fill in the Blanks

1. SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 2;
Hint: Type the keyword that filters groups after GROUP BY using an aggregate condition.
2. SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 5;
Hint: Type the aggregate function that works out the average of 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 studentId, AVG(mark) FROM results GROUP BY studentId HAVING AVG(mark) >= 75;
Hint: Type the comparison operator meaning at least as large as the value.
5. SELECT house, SUM(points) FROM students GROUP BY house HAVING SUM(points) > 200;
Hint: Type the clause that must come first to make the groups that HAVING then filters.

Spot the Error

Question 1
A SELECT house, COUNT(*)
B FROM students GROUP house
C HAVING COUNT(*) > 2;
Line B — GROUP BY needs the word BY. The line should be FROM students GROUP BY house so the rows are grouped before HAVING filters the groups.
Question 2
A SELECT house, AVG(points)
B FROM students GROUP BY house
C WHERE AVG(points) > 100;
Line C — WHERE filters single rows before grouping, so it cannot use an aggregate like AVG(). To filter groups by their average, use HAVING AVG(points) > 100;
Question 3
A SELECT category, AVG(price)
B FROM products
C GROUP BY category
D HAVING price > 10;
Line D — HAVING must test an aggregate value for each group. It should be HAVING AVG(price) > 10; because price alone is a single row value, not a group value.
Question 4
A SELECT studentId COUNT(*)
B FROM orders
C GROUP BY studentId
D HAVING COUNT(*) >= 2;
Line A — Columns in the SELECT list must be separated by a comma. It should be SELECT studentId, COUNT(*).
Question 5
A SELECT studentId, COUNT(*)
B FROM orders ORDER BY studentId
C HAVING COUNT(*) > 1;
Line B — ORDER BY only sorts rows and does not make groups. HAVING needs GROUP BY, so the line should be FROM orders GROUP BY studentId.

Student with a class code? Join your class →