This SQL tutorial focuses on the MySQL Having Clause, and provides explanations, examples and exercises. For this lesson’s exercises use this link.
This tutorial is a part of several posts explaining how to use the MySQL Group Functions. To read additional posts regarding this subject, please use the following links:
- MySQL group functions
- MySQL Group By clause
- MySQL Having Clause
The MySQL HAVING Clause
The MySQL HAVING clause allows filtering of aggregated results produced by the MySQL GROUP BY clause. In the same way you used MySQL WHERE clause to restrict rows, you use the MySQL HAVING clause to restrict groups.
SELECT column_name , group_function(column_name) FROM table_name WHERE condition GROUP BY column_name HAVING condition
The categories where the average price is greater than 50:
SELECT category_id , AVG(price) FROM books GROUP BY category_id HAVING AVG(price) > 50
In a single query you can use both MySQL HAVING and WHERE clauses. Out of categories 80, 50, and 90, the categories where the average price is higher than 50:
SELECT category_id , AVG(price) FROM books WHERE category_id IN (50, 80, 90) GROUP BY category_id HAVING AVG(price) > 50
You can filter based on another group function than the one that appears in the MySQL SELECT statement:
SELECT category_id , AVG(price) FROM books WHERE category_id IN (50, 80, 90) GROUP BY category_id HAVING MAX(price) > 50