Skip to Main Content
Mastering Oracle SQL
book

Mastering Oracle SQL

by Sanjay Mishra, Alan Beaulieu
April 2002
Intermediate to advanced content levelIntermediate to advanced
336 pages
9h 58m
English
O'Reilly Media, Inc.
Content preview from Mastering Oracle SQL

The HAVING Clause

The HAVING clause is closely associated with the GROUP BY clause. The HAVING clause is used to put a filter on the groups created by the GROUP BY clause. If a query has a HAVING clause along with a GROUP BY clause, the result set will include only the groups that satisfy the condition specified in the HAVING clause. Let’s look at some examples that illustrate this. The following query returns the number of orders per customer:

            SELECT CUST_NBR, COUNT(ORDER_NBR) 
            FROM CUST_ORDER 
            GROUP BY CUST_NBR
            HAVING CUST_NBR < 260;

  CUST_NBR COUNT(ORDER_NBR)
---------- ----------------
       201                2
       231                6
       244                2
       255                6

Notice that the output only includes customers with numbers below 260. That’s because the HAVING clause specified CUST_NBR < 260 as a condition. Orders for all customers were counted, but only those groups that matched the specified HAVING condition were returned as the result.

The previous example is a poor use of the HAVING clause, because that clause only references unsummarized data. It’s more efficient to use WHERE CUST_NBR < 260 instead of HAVING CUST_NBR < 260, because the WHERE clause eliminates rows prior to summarization, whereas HAVING eliminates groups post-summarization. A better version of the previous query would be:

SELECT CUST_NBR, COUNT(ORDER_NBR) 
FROM CUST_ORDER 
WHERE CUST_NBR < 260;

The next example shows a more appropriate use of the HAVING clause:

            SELECT CUST_NBR, COUNT(ORDER_NBR) 
            FROM CUST_ORDER 
            GROUP BY CUST_NBR
            HAVING COUNT(ORDER_NBR) > 2; CUST_NBR ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL

Oracle SQL

Dan Hotka
Oracle SQL Developer

Oracle SQL Developer

Ajith Narayanan, Susan Harper

Publisher Resources

ISBN: 0596001290Supplemental ContentCatalog PageErrata