HAVING Clause in SQL
The HAVING clause in SQL helps you filter grouped data. You use it after the GROUP BY clause. Most of the time, it works with aggregate functions like COUNT, SUM, and AVG.
In simple words, HAVING checks results after grouping, not before.
Why the HAVING Clause Is Important
First, SQL groups rows using GROUP BY.
Then, HAVING filters those groups based on a condition.
You need HAVING because SQL does not allow aggregate functions in the WHERE clause. Therefore, whenever you want to filter totals or averages, HAVING becomes the right choice.
HAVING helps you
create clean reports
filter summary data
analyze grouped results
Sample Table
students
id | name | city | marks
1 | Rahul | Delhi | 85
2 | Neha | Mumbai | 92
3 | Amit | Delhi | 78
4 | Priya | Pune | 88
5 | Karan | Mumbai | 90
Basic HAVING Example
SELECT city, COUNT(*)
FROM students
GROUP BY city
HAVING COUNT(*) > 1;
This query shows only cities that have more than one student.
HAVING with SUM
SELECT city, SUM(marks)
FROM students
GROUP BY city
HAVING SUM(marks) > 160;
Here, SQL first groups data by city. After that, it keeps only cities with high total marks.
HAVING with AVG
SELECT city, AVG(marks)
FROM students
GROUP BY city
HAVING AVG(marks) >= 85;
This query returns cities where students score well on average.
Using WHERE and HAVING Together
SELECT city, AVG(marks)
FROM students
WHERE marks > 80
GROUP BY city
HAVING AVG(marks) > 85;
This query works step by step.
First, WHERE filters rows.
Next, GROUP BY creates groups.
Finally, HAVING filters those groups.
GROUP BY vs HAVING in SQL
Understanding the difference between GROUP BY and HAVING makes SQL much easier.
| Feature | GROUP BY | HAVING |
|---|---|---|
| Main role | Groups rows | Filters groups |
| Works on | Row values | Group results |
| Used with | SELECT | GROUP BY |
| Aggregate functions | Not required | Required |
| Execution order | Before HAVING | After GROUP BY |
Common Mistake
-- This is incorrect
SELECT city, AVG(marks)
FROM students
WHERE AVG(marks) > 85
GROUP BY city;
This query fails because WHERE cannot use aggregate functions.
When to Use HAVING
Use HAVING when
you work with totals or averages
you filter grouped data
you create summary reports
Summary
GROUP BY creates groups of data.
HAVING filters those groups.
WHERE filters rows before grouping.
Once you understand this flow, writing reports in SQL becomes much easier.
Check out our resources!
- Bootstrap Templates: Explore our Bootstrap Projects section.
- Free E-Books: Download your Free E-Books here.
