Oracle Having Clause

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