Filtering Groups with HAVING Clause

When you group data using GROUP BY , you often want to apply conditions on the groups themselves , not on individual rows. That’s where HAVING comes in.

🧠 Key Point:

• WHERE filters rows before grouping.
• HAVING filters groups after grouping.

🔧 Syntax

SELECT column_name, AGGREGATE_FUNCTION(column_name) 
 FROM table_name 
 GROUP BY column_name 
 HAVING condition ;  

📘 Our Sample Table: students

id	name	age	course	marks
1	Rahul Sharma	21	Advanced SQL	90.00
2	Anjali Kapoor	22	Advanced SQL	91.75
3	Vikas Gupta	24	Advanced SQL	91.00
4	Riya Jain	20	Python	92.50
5	Aman Verma	24	Advanced SQL	76.00

🎯 Example 1: Show only those courses where average marks are above 90

SELECT course, AVG (marks) AS average_marks 
 FROM students 
 GROUP BY course 
 HAVING AVG (marks) > 90 ;  

Output:

course	average_marks
Python	92.50

🎯 Example 2: Courses with more than 2 students

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

Output:

course	total_students
Advanced SQL	4

🧮 Example 3: Combine WHERE and HAVING

SELECT course, AVG (marks) AS avg_marks 
 FROM students 
 WHERE age > 21 
 GROUP BY course 
 HAVING AVG (marks) > 85 ;  

Explanation:

• WHERE age >21 filters the rows before grouping.
• HAVING AVG(marks) >85 filters the groups after aggregation.

🔄 Difference Between WHERE and HAVING

Feature	WHERE	HAVING
Applied on	Individual rows	Groups of rows
Works with	All columns	Aggregated values only
Executes	Before GROUP BY	After GROUP BY

⚠️ Common Errors

❌ This won’t work:

SELECT course, AVG (marks) 
 FROM students 
 HAVING age > 21 
 GROUP BY course;  

✅ Use WHERE for row-level filtering:

SELECT course, AVG (marks) 
 FROM students 
 WHERE age > 21 
 GROUP BY course;  

📌 Summary

• Use HAVING to filter grouped results .
• It is used afterGROUP BY .
• Combine it with aggregate functions like COUNT() , SUM() , AVG() , etc.
• Use WHERE for rows, HAVING for groups.