MySQL HAVING Clause: Definition, Syntax, and Examples

Unlike WHERE, which filters rows before grouping,
HAVING filters data after the aggregation using functions like SUM(), COUNT(), AVG(), MAX(), and MIN().

HAVING Syntax

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

column_name → Column to display or group by.
aggregate_function() → Functions like SUM(), COUNT(), AVG(), etc.
HAVING → Filter the results of the aggregation.

MySQL HAVING Examples

We have two tables:

students Table

student_id	name	gender	city	age	score	course_id	email	phone_number
1	Alice	Female	Delhi	20	85	101	NULL	1234567890
2	Bob	Male	Mumbai	22	75	NULL	bob@email.com	NULL
3	Charlie	Male	Delhi	21	95	102	NULL	NULL
4	David	Male	Bangalore	23	65	101	david@email.com	NULL
5	Eve	Female	Mumbai	20	80	103	NULL	NULL
6	Frank	Male	Delhi	22	90	103	NULL	NULL
7	Alice	Female	Mumbai	19	60	102	NULL	NULL

courses Table

course_name	course_name
101	Math
102	Science
103	History
104	English

The following SQL statement find cities with average score greater than 80.

Example

SELECT city, AVG(score) AS avg_score

FROM students

GROUP BY city

HAVING AVG(score) > 80;

Try it yourself

Output:

city	avg_score
Delhi	90.0

Explanation:

AVG(score) calculates the average score per city.
HAVING AVG(score) > 80 filters the cities where the average score is above 80.

HAVING Examples with join

The following SQL statement find courses with average score greater than 80.

Example

SELECT c.course_name, AVG(s.score) AS avg_score

FROM students s

JOIN courses c ON s.course_id = c.course_id

GROUP BY c.course_name

HAVING AVG(s.score) > 80;

Try it yourself

Output:

course_name	avg_score
History	85.0

Explanation:

JOIN combines students and courses based on course_id.
GROUP BY groups the data by course_name.
AVG(score) calculates the average score for each course.
HAVING filters courses with an average score greater than 80.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.