WS WhereIsStuff
Tutorial Guides
Log In
Sign Up

Advertisement

Google Ad Slot: content-top

MySQL HAVING

Previous Next

MySQL HAVING Clause

The HAVING clause in MySQL is used to filter the results of a GROUP BY operation.

  • 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