Velamuvi AIAcademy

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
In this lesson you will learn
  • 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.

Try it yourselfSQL
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.

Try it yourselfSQL
SELECT course_id, COUNT(*) AS enrolled
FROM enrollments
GROUP BY course_id
HAVING COUNT(*) >= 3;

WHERE or HAVING?

WHEREHAVING
FiltersIndividual rowsWhole groups
RunsBefore groupingAfter grouping
Can use aggregatesNoYes
ExampleWHERE progress = 100HAVING 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.

Try it yourselfSQL
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?"

Try it yourselfSQL
SELECT course_id, COUNT(*) AS finished
FROM enrollments
WHERE progress = 100
GROUP BY course_id
HAVING COUNT(*) >= 2
ORDER BY finished DESC, course_id;
  1. WHERE progress = 100 removes unfinished enrollments.
  2. GROUP BY course_id puts the rest into one group per course.
  3. HAVING COUNT(*) >= 2 removes the small groups.
  4. ORDER BY sorts 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.

StepClauseWhat happens
1FROMFind the table
2WHERERemove rows that fail the condition
3GROUP BYPut the remaining rows into groups
4HAVINGRemove groups that fail the condition
5SELECTWork out the columns to return
6ORDER BYSort the result
7LIMITKeep 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.

Try it yourselfSQL
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

Practice exercise

Find the students who are enrolled in more than one course. Show each student_id and their number of enrollments.

Your solutionSQL
-- Show the student_id and number of enrollments
-- for every student who has MORE THAN ONE enrollment.
-- Name the count column "courses".
Knowledge check

You want only the groups whose total is above 1000. Which clause do you use?

Summary

  • HAVING filters groups, and it can use aggregate functions.
  • WHERE filters 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.