HAVING
clause specifies a search condition by using an aggregate function.
It will filter out the records returned from a GROUP BY
clause that do not fulfill a specified condition.
HAVING
and WHERE
clause:
WHERE | HAVING |
---|---|
The GROUP BY clause appears after the WHERE clause. | The GROUP BY clause appears before the HAVING clause. |
The WHERE clause can’t work with an aggregate function. | The HAVING clause can work with an aggregate function. |
The WHERE clause filters particular records. | The HAVING clause filters the group of records. |
GROUP BY
clause is as follows:
SELECT column_1, column_2,...
selects the columns you want to display.FROM table_name
selects the table where the data comes from.GROUP BY column_name(s)
lists the columns you want to group in the GROUP BY clause.HAVING condition_aggregate_function
provides the condition for filtering rows, which the GROUP BY
clause forms. The condition can use an aggregate function, such as SUM()
, COUNT()
, MIN()
, and so on.HAVING
Clause with AVG
FunctionAVG
aggregate function to filter the student ID with the subject which has an average score of more than 80:
HAVING
Clause with COUNT
FunctionHAVING
Clause with MAX
FunctionHAVING
with JOIN
ConditionJOIN
clause and apply a condition on the score_stat
column where the values should be equal to FAILED, as shown in the following query:
JOIN
clause will combine the two tables.GROUP BY
clause will filter all records from both tables based on the specified columns.HAVING
clause, then, will filter the records returned from the GROUP BY
clause according to the specified condition.