5.5

Using the HAVING and WHERE Clauses Together

Below is an example of a statement that includes both the HAVING and WHERE clause in the same SQL statement.

USE bike;
SELECT category_id, AVG(list_price)
FROM product
WHERE model_year = 2016
GROUP BY category_id
HAVING AVG(list_price) > 1000

 Output:

ag_05.png

WHERE model_year = 2016

  • The WHERE clause executes before the GROUP BY
  • You can refer to columns not defined in the SELECT
  • You cannot use aggregate functions in the WHERE

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