HAVING Clause in MySQL
Introduction
Filtering grouped data requires special handling beyond basic WHERE.
The HAVING clause filters groups after GROUP BY aggregation.
It is widely used for:
- Filtering aggregate results (AVG > threshold)
- Conditional summaries
- Advanced reporting queries
What is a HAVING Clause
The HAVING clause filters which groups to include based on aggregate conditions.
It works only with GROUP BY and aggregate functions.
Basic Syntax
sql
SELECT column_name, AGGREGATE_FUNCTION(column)
FROM table_name
GROUP BY column_name
HAVING condition;
Example
sql
SELECT city, AVG(marks) as avg_marks
FROM Students
GROUP BY city
HAVING AVG(marks) > 75;
Shows only cities with average marks above 75.
HAVING Multiple Conditions
Multiple filters on aggregates.
sql
SELECT city, COUNT(*) as student_count
FROM Students
GROUP BY city
HAVING COUNT(*) > 10 AND AVG(marks) > 70;
HAVING vs WHERE
| Aspect | WHERE | HAVING |
| When Applied | Before GROUP BY | After GROUP BY |
| Filters | Individual rows | Groups/aggregates |
| Aggregate Functions | No | Yes |
| Execution Order | Earlier | Later |
Example: WHERE + HAVING
sql
SELECT city, AVG(marks) as avg_marks
FROM Students
WHERE marks >= 50
GROUP BY city
HAVING AVG(marks) > 75;
WHERE filters rows first, HAVING filters groups.
HAVING with ORDER BY
Sort filtered groups.
sql
SELECT city, AVG(marks) as avg_marks
FROM Students
GROUP BY city
HAVING COUNT(*) > 5
ORDER BY avg_marks DESC;
HAVING with Multiple Aggregates
Filter using different aggregates.
sql
SELECT city, AVG(marks), COUNT(*)
FROM Students
GROUP BY city
HAVING AVG(marks) > 70 AND COUNT(*) > 3;
HAVING with JOIN
Filter grouped JOIN results.
sql
SELECT Courses.course_name, COUNT(Students.id) as enrollment
FROM Students
JOIN Courses ON Students.course_id = Courses.id
GROUP BY Courses.course_name
HAVING COUNT(Students.id) > 5;
HAVING with Subqueries
Complex aggregate conditions.
sql
SELECT city, AVG(marks)
FROM Students
GROUP BY city
HAVING AVG(marks) > (
SELECT AVG(marks) FROM Students
);
HAVING with ROLLUP
Filters ROLLUP subtotals.
sql
SELECT city, AVG(marks)
FROM Students
GROUP BY city WITH ROLLUP
HAVING AVG(marks) > 70;
Execution Order with HAVING
HAVING is applied after:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
Performance Considerations
- HAVING processes after grouping (use WHERE when possible)
- Index GROUP BY columns
- Avoid complex functions in HAVING
- Limit rows with WHERE before GROUP BY
Important Notes
- HAVING without GROUP BY treats the whole result as one group
- Can use column aliases in HAVING
- Multiple HAVING conditions with AND/OR
- Works with COUNT(*), not just grouped columns
- MySQL allows non-standard HAVING usage
Example Scenario
Find cities with >10 students and avg marks >80:
sql
SELECT city, COUNT(*), ROUND(AVG(marks),1) as avg_marks
FROM Students
GROUP BY city
HAVING COUNT(*) > 10 AND AVG(marks) > 80
ORDER BY avg_marks DESC;
Common Mistakes
- Using HAVING instead of WHERE for row filters
- Aggregate functions in the WHERE clause
- Forgetting GROUP BY with HAVING
- HAVING before GROUP BY (wrong order)
- Complex conditions are slowing queries
Key Points to Remember
- HAVING filter groups after GROUP BY
- WHERE filters rows before GROUP BY
- Only aggregate functions/aliases in HAVING
- Essential for conditional aggregate queries
- Combine WHERE + HAVING for best performance
- Supports AND/OR multiple conditions