5.5
Using the HAVING Clause
Filtering Aggregate Functions With The HAVING Clause
- The HAVING CLAUSE allows you to use an aggregate function as a filter. This is not allowed in a WHERE clause.
- Any columns or expressions you want to use in a HAVING clause, MUST BE DEFINED IN THE SELECT CLAUSE as well.
Code Sample:
USE bike;
SELECT category_id, AVG(list_price)
FROM product
GROUP BY category_id
HAVING AVG(list_price) > 1000
Output:
We previously discussed the preceding lines of code for this query so we will focus solely on the HAVING clause.
HAVING AVG(list_price) > 1000
- The HAVING clause executes after the GROUP BY clause but before the SELECT
- If you use an aggregate function in the HAVING clause, you must include the same aggregate function in the SELECT
- If you reference a column or expression in the HAVING clause, you must include the same column or expression in the SELECT
- You cannot use aggregate functions in a WHERE clause