This SQL tutorial focuses on the Oracle 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 Oracle Group Functions. To read additional posts regarding this subject, please use the following links:
- Oracle group functions
- Oracle Group By clause
- Oracle Having Clause
The Oracle HAVING Clause
The Oracle HAVING clause allows filtering of aggregated results produced by the Oracle GROUP BY clause. In the same way you used Oracle WHERE clause to restrict rows, you use the Oracle 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 unit_price is greater than 5000:
SELECT category_id , AVG(unit_price) FROM products GROUP BY category_id HAVING AVG(unit_price) > 5000
In a single query you can use both Oracle HAVING and WHERE clauses. Out of categories 80, 50, and 90, the categories where the average price is higher than 5000:
SELECT category_id , AVG(unit_price) FROM products WHERE category_id IN (50, 80, 90) GROUP BY category_id HAVING AVG(unit_price) > 5000
You can filter based on another group function than the one that appears in the Oracle SELECT statement:
SELECT category_id , AVG(unit_price) FROM products WHERE category_id IN (50, 80, 90) GROUP BY category_id HAVING MAX(unit_price) > 5000