What is MySQL HAVING?
This is similar to the WHERE clause in MySQL but since we can not use the aggregate function in the WHERE condition MySQL HAVING clause was introduced.
The syntax for MySQL HAVING clause:
SELECT
column_name(s), function(column_name)
FROM
table_name
GROUP BY column_name(s)
HAVING function(column_name) condition;
We have already seen what the aggregate function in MySQL GROUP BY is. Now we will see how we can use conditions on those aggregate functions using a having clause. We will add new entries for the subject name ‘Language’ for 3 students.
The student_report table has the following data.
HAVING clause using count() function condition, We will get the list of the subjects whose count is greater than 3
SELECT
subject, COUNT(subject) AS subject_rows
FROM
student_report
GROUP BY subject
HAVING COUNT(subject) > 3
ORDER BY subject;
Note that we use the HAVING clause after GROUP BY and before ORDER BY.
As shown below, we can use the WHERE condition if required before the GROUP BY clause.
SELECT
subject, COUNT(subject) AS subject_rows
FROM
student_report
WHERE subject <> ‘French’
GROUP BY subject
HAVING COUNT(subject) > 3;
We will see one more example using the sum() function to get the subject details whose sum of marks is greater than 200
SELECT
subject, sum(marks) AS subject_rows
FROM
student_report
GROUP BY subject
HAVING sum(marks) > 30
ORDER BY subject;
Example for Min() function using having clause to get the subjects having min marks less than 50.
SELECT
subject, min(marks) AS subject_rows
FROM
student_report
GROUP BY subject
HAVING min(marks) < 50
ORDER BY subject;
The example for the max() function uses a having clause to get the list of subjects whose maximum value is greater than 80.
SELECT
subject, Max(marks) AS subject_rows
FROM
student_report
GROUP BY subject
HAVING max(marks) > 80;
Using the avg() function in having clause to get the list of subjects whose average marks are greater than 60.
SELECT
subject, avg(marks) AS subject_rows
FROM
student_report
GROUP BY subject
HAVING avg(marks) > 60;
