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