HAVING — Filtering Groups

7 min read

Filtering Aggregated Results with HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. You need HAVING whenever your condition references an aggregate function.

sql

-- Cities with more than 5 students
SELECT city, COUNT(*) AS cnt
FROM students
GROUP BY city
HAVING COUNT(*) > 5
ORDER BY cnt DESC;

-- Courses where the average score is below 60
SELECT course_id, AVG(score) AS avg_score
FROM enrolments
WHERE score IS NOT NULL
GROUP BY course_id
HAVING AVG(score) < 60;

-- Categories with total revenue above 10000
SELECT category, SUM(price) AS total
FROM courses
GROUP BY category
HAVING SUM(price) > 10000;

The full logical order of a SELECT query is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Understanding this order explains why you cannot reference a column alias from SELECT inside HAVING.

When both WHERE and HAVING could filter the same data, prefer WHERE. Filtering rows before grouping is cheaper because the database processes fewer rows during aggregation.

Ready to test yourself?

Practice SQL— quiz & coding exercises