← QCE Digital Solutions Unit 4

SQL HAVING

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

Part of: QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 70

Understanding SQL HAVING in QCE Digital Solutions Unit 4

What it does: WHERE yearLevel = 7 removes rows first, then GROUP BY groups the remaining rows and counts them. HAVING must come after GROUP BY. Putting it before GROUP BY is a syntax error. AVG(mark) is an aggregate calculated for each group, so it goes in HAVING. A plain row condition like studentId = 1 goes in WHERE. HAVING filters groups, so it normally follows GROUP BY. It does not replace GROUP BY.

Common mistakes: WHERE filters rows before grouping, so it cannot use an aggregate like COUNT(*). Filtering groups by an aggregate needs HAVING COUNT(*) > 2 after GROUP BY. The table is called students, not student. The FROM clause must name a real table or the query fails.

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 334–401) and word search (Modules 127–401).

Take this worksheet further — free with Cloodit

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

Learning
Duo
You want only the houses that have more than 2 students. Which clause filters on COUNT(*) after grouping?
WHERE COUNT(*) > 2HAVING COUNT(*) > 2
Matches
SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 2 GROUP BY house;
↓
WHERE runs before grouping, so it cannot use an aggregate function like COUNT(*). Use HAVING COUNT(*) > 2 after GROUP BY instead.
Applying
Meanings
Term
HAVING
Meaning
Filters groups after GROUP BY using an aggregate condition, e.g. HAVING COUNT(*) > 2. WHERE filters rows before grouping. Order: WHERE, GROUP BY, HAVING.
Practice
SELECT category, COUNT(*) AS items
Testing
Gaps
COUNT(*) > 2;
Check Answer
Spots
3GROUP BY house
4WHERE COUNT(*) > 2;

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. You want only the houses that have more than 2 students. Which clause filters on COUNT(*) after grouping?
A. HAVING COUNT(*) > 2 ✓
B. WHERE COUNT(*) > 2
WHERE runs before grouping, so it can't use an aggregate like COUNT(*). HAVING filters the groups after GROUP BY.
2. Which statement about WHERE and HAVING is true?
A. WHERE filters groups after grouping; HAVING filters rows before grouping
B. WHERE filters rows before grouping; HAVING filters groups after grouping ✓
WHERE removes individual rows first. GROUP BY then makes the groups, and HAVING keeps or removes whole groups.
3. Which is the correct clause order in a grouped query?
A. HAVING, GROUP BY, WHERE
B. WHERE, GROUP BY, HAVING ✓
The order is WHERE, then GROUP BY, then HAVING. Rows are filtered, grouped, and then the groups are filtered.
4. Which query correctly lists houses with more than 2 students?
A. SELECT house, COUNT(*) FROM students GROUP BY house HAVING COUNT(*) > 2; ✓
B. SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 2 GROUP BY house;
HAVING must come after GROUP BY. Putting it before GROUP BY is a syntax error.
5. You want to ignore students in Year 7 BEFORE counting students in each house. Which condition goes in WHERE?
A. WHERE yearLevel > 7 ✓
B. HAVING yearLevel > 7
yearLevel is a condition on individual rows, so it belongs in WHERE. HAVING is for conditions on aggregates of a group.

Match the Pairs

HAVING COUNT(*) > 2 → Keeps only the groups that contain more than two rows
GROUP BY house HAVING SUM(points) >= 200 → Keeps only the houses whose total points are at least 200
WHERE filters individual rows before they are grouped, so it cannot use an aggregate like COUNT(*) → SELECT house, COUNT(*) FROM students WHERE yearLevel = 7 GROUP BY house;
The correct clause order is WHERE, then GROUP BY, then HAVING → SELECT house, COUNT(*) FROM students WHERE points > 50 GROUP BY house HAVING COUNT(*) > 2;
SELECT house, COUNT(*) FROM students WHERE COUNT(*) > 2 GROUP BY house; → WHERE runs before grouping, so it cannot use an aggregate function like COUNT(*). Use HAVING COUNT(*) > 2 after GROUP BY instead.
SELECT house, COUNT(*) FROM students HAVING COUNT(*) > 2 GROUP BY house; → HAVING is in the wrong place. It must come after GROUP BY, not before it.

Key Term

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

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, COUNT(*) FROM products WHERE price > 2 GROUP BY category;
Hint: Type the clause that filters individual rows before they are grouped.
3. SELECT subjectId, AVG(mark) FROM results GROUP BY subjectId HAVING AVG(mark) > 70;
Hint: Type the aggregate function that works out each group's average in the HAVING condition.
4. SELECT studentId, COUNT(*) FROM orders GROUP BY studentId HAVING COUNT(*) > 1;
Hint: Type the two-word clause that builds the groups and must come before HAVING.
5. SELECT house, SUM(points) FROM students GROUP BY house HAVING SUM(points) > 200;
Hint: Type the comparison operator that keeps only groups whose total is larger than the number.

Spot the Error

Question 1
A SELECT house, COUNT(*)
B FROM students
C GROUP BY house
D WHERE COUNT(*) > 2;
Line D — WHERE filters rows before grouping, so it cannot use an aggregate like COUNT(*). Filtering groups by an aggregate needs HAVING COUNT(*) > 2 after GROUP BY.
Question 2
A SELECT house, COUNT(*)
B FROM student
C WHERE yearLevel > 7
D GROUP BY house
E HAVING COUNT(*) > 1;
Line B — The table is called students, not student. The FROM clause must name a real table or the query fails.
Question 3
A SELECT house, COUNT(*)
B FROM students HAVING points>50
C GROUP BY house
D HAVING COUNT(*) > 2;
Line B — A condition on single rows (points>50) is a WHERE condition and runs before grouping. HAVING comes after GROUP BY and is for aggregate conditions, so this should be WHERE points>50.
Question 4
A SELECT house COUNT(*)
B FROM students
C GROUP BY house
D HAVING COUNT(*) > 2;
Line A — Columns and aggregates in SELECT must be separated by a comma. It should be SELECT house, COUNT(*).
Question 5
A SELECT subjectId, AVG(marks)
B FROM results
C GROUP BY subjectId
D HAVING AVG(mark) > 70;
Line A — The column in the results table is mark, not marks. It should be AVG(mark), the same as in the HAVING line.

Student with a class code? Join your class →