HAVING Clause

Introduction

In the previous lesson, GROUP BY let you summarize students per grade — counting them, averaging their marks, and so on. But what if you only want to see the grades that have more than 2 students? WHERE cannot help here, because WHERE filters individual rows before any grouping happens.

HAVING solves exactly this problem. It filters groups after GROUP BY has already summarized the data, letting you keep only the groups that meet a certain condition.

What is HAVING?

HAVING is a clause used to filter the results of a GROUP BY, based on the value of an aggregate function like COUNT(), AVG(), or SUM(). It is written directly after GROUP BY.

Using the same students table from the previous lesson:

Grouping by grade and counting gives three groups: A has 3 students, B has 2, and C has 1. HAVING lets you keep only the groups whose count satisfies a condition.

SELECT grade, COUNT(*) AS total_students
FROM students
GROUP BY grade
HAVING COUNT(*) > 2;

+-------+----------------+
| grade | total_students |
+-------+----------------+
| A     | 3              |
+-------+----------------+

Only grade "A" is returned, because it is the only group where COUNT(*) is greater than 2. Grades B (2 students) and C (1 student) are filtered out.

WHERE vs HAVING

The single most important distinction is timing: WHERE filters individual rows before grouping happens, while HAVING filters entire groups after grouping and aggregation have already happened.

-- WHERE: keep only rows where marks is above 60, BEFORE grouping
SELECT grade, COUNT(*) AS total_students
FROM students
WHERE marks > 60
GROUP BY grade;

+-------+----------------+
| grade | total_students |
+-------+----------------+
| A     | 3              |
| B     | 2              |
+-------+----------------+

Worked Example: Grades With More Than 2 Students

You can combine WHERE and HAVING in the same query — WHERE filters raw rows first, then GROUP BY summarizes what remains, and HAVING filters those summarized groups.

SELECT grade, COUNT(*) AS total_students, AVG(marks) AS average_marks
FROM students
WHERE marks >= 50
GROUP BY grade
HAVING COUNT(*) > 1
ORDER BY total_students DESC;

+-------+----------------+---------------+
| grade | total_students | average_marks |
+-------+----------------+---------------+
| A     | 3              | 91.6667       |
| B     | 2              | 71.5000       |
+-------+----------------+---------------+

Here, WHERE marks >= 50 keeps every row (all marks already satisfy this in the sample data), GROUP BY grade creates the three grade groups, HAVING COUNT(*) > 1 removes grade "C" (only 1 student), and ORDER BY sorts the remaining groups by size.

Clause Order

MySQL requires clauses to be written in a specific order in your query. Understanding this order helps you reason about exactly what each clause is filtering.

SELECT grade, COUNT(*) AS total_students
FROM students
WHERE marks >= 50        -- 1. filter rows
GROUP BY grade            -- 2. group remaining rows
HAVING COUNT(*) > 1       -- 3. filter groups
ORDER BY total_students DESC; -- 4. sort final result

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

HAVING completes the picture started by GROUP BY: it lets you filter down to only the groups that matter, based on aggregate conditions like a minimum count or average.

In this lesson, you learned what HAVING does, how it differs from WHERE, and the strict clause order MySQL enforces. Next, you will step back and get a conceptual overview of SQL operators as a whole.