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 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 with 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 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 WHERE clause
  • Forgetting GROUP BY with HAVING
  • HAVING before GROUP BY (wrong order)
  • Complex conditions slowing queries

Key Points to Remember

  • HAVING filters 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