Lesson 8 of 17 · Summarising Data
HAVING
Learn the SQL HAVING clause and how it differs from WHERE. Filter groups by their count, sum or average, with clear runnable examples.
- 7 min read
- Beginner
- Reviewed
- How to keep only the groups that pass a test
- The difference between WHERE and HAVING
- The order in which a database processes a query
A filter for groups
GROUP BY gives you one row per group. What if you only want some of the groups? For example, only the courses with at least three students.
You might try WHERE.
SELECT course_id, COUNT(*) AS enrolled FROM enrollments WHERE COUNT(*) >= 3 GROUP BY course_id;
It fails with misuse of aggregate: COUNT(). WHERE checks rows one at a time, before any groups exist, so it cannot know a group's count.
HAVING is the answer. It filters after grouping, so it can use aggregates.
SELECT course_id, COUNT(*) AS enrolled FROM enrollments GROUP BY course_id HAVING COUNT(*) >= 3;
WHERE or HAVING?
WHERE | HAVING | |
|---|---|---|
| Filters | Individual rows | Whole groups |
| Runs | Before grouping | After grouping |
| Can use aggregates | No | Yes |
| Example | WHERE progress = 100 | HAVING COUNT(*) >= 3 |
A simple test: if the condition is about a single row, use WHERE. If it is about a summary of a group, use HAVING.
Any aggregate can be used
The condition in HAVING does not have to match what you select. This finds the students whose average score is 90 or more.
SELECT student_id, ROUND(AVG(score), 1) AS average_score FROM enrollments GROUP BY student_id HAVING AVG(score) >= 90;
Using both together
Many real queries need both filters. Read this one as: "Among finished enrollments, which courses have at least two?"
SELECT course_id, COUNT(*) AS finished FROM enrollments WHERE progress = 100 GROUP BY course_id HAVING COUNT(*) >= 2 ORDER BY finished DESC, course_id;
WHERE progress = 100removes unfinished enrollments.GROUP BY course_idputs the rest into one group per course.HAVING COUNT(*) >= 2removes the small groups.ORDER BYsorts what is left.
How a database processes a query
You write a query starting with SELECT. The database processes it in a different order, and knowing that order explains most beginner errors.
| Step | Clause | What happens |
|---|---|---|
| 1 | FROM | Find the table |
| 2 | WHERE | Remove rows that fail the condition |
| 3 | GROUP BY | Put the remaining rows into groups |
| 4 | HAVING | Remove groups that fail the condition |
| 5 | SELECT | Work out the columns to return |
| 6 | ORDER BY | Sort the result |
| 7 | LIMIT | Keep only the first rows |
This is why WHERE cannot use an aggregate: at step 2 the groups do not exist yet. It is also why ORDER BY can use an alias: aliases are created at step 5, before sorting.
A classic use: finding duplicates
Grouping by a column and keeping groups with a count above one finds repeated values. This lists countries that have more than one student.
SELECT country, COUNT(*) AS students FROM students GROUP BY country HAVING COUNT(*) > 1;
The same pattern finds duplicate email addresses, repeated order numbers and other data problems.
Common mistakes
Find the students who are enrolled in more than one course. Show each student_id and their number of enrollments.
-- Show the student_id and number of enrollments -- for every student who has MORE THAN ONE enrollment. -- Name the count column "courses".
You want only the groups whose total is above 1000. Which clause do you use?
Summary
HAVINGfilters groups, and it can use aggregate functions.WHEREfilters rows, before grouping.- A query can use both.
- The database processes a query in the order
FROM,WHERE,GROUP BY,HAVING,SELECT,ORDER BY,LIMIT.
Next: the value that means "we do not know".
Finished reading? Tick it off to track your progress.