0% completed
HAVING
In MySQL, the HAVING
clause is used in conjunction with the GROUP BY
clause to filter the results of a query based on aggregate functions.
The HAVING
clause is applied after the GROUP BY
clause and allows you to specify conditions for groups of rows. It is primarily used with aggregate functions like SUM,
COUNT,
AVG,
etc.
Syntax
SELECT column_name FROM table_name WHERE condition GROUP BY column_name HAVING condition
Example
Suppose we have a Students
table as shown below:
Now, let's say you want to find courses where the number of students who scored above 79 is greater than 1. You can use the HAVING
clause for this:
SELECT course, COUNT(student_id) as total_students FROM Students WHERE score > 79 GROUP BY course HAVING total_students > 1;
In this example:
- We select the
course
column and count the number of students who scored above 79 usingCOUNT(student_id)
for each course. - We use
WHERE score > 79
to filter out individual rows where the score is not greater than 79 before grouping. - We use
GROUP BY course
to group the data by the course. - The
HAVING
clause filters out groups where the number of students is not greater than 1.
The result of this query would be:
This result includes both "Math" and "English" courses because they have more than one student who scored above 79.
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible