MySQL Having Clause

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