4.3 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 COUNT(ORDER_NBR)
---------- ----------------
       231                6
       255                6

Note the use of an aggregate function in the HAVING clause. This is an appropriate use for HAVING, because the results of the aggregate function cannot be determined until after the grouping takes place.

The syntax for the HAVING clause is similar to that of the WHERE clause. However, there is one restriction on the condition in the HAVING clause. The condition can only refer to expressions in the SELECT list or the GROUP BY clause. If we specify an expression in the HAVING clause that isn't in the SELECT list or the GROUP BY clause, we will get an error. For example:

SELECT CUST_NBR, COUNT(ORDER_NBR) 
FROM CUST_ORDER 
GROUP BY CUST_NBR
HAVING ORDER_DT < SYSDATE;
HAVING ORDER_DT < SYSDATE
       *
ERROR at line 4:
ORA-00979: not a GROUP BY expression

The order of the GROUP BY clause and the HAVING clause in a SELECT statement is not important. We can specify the GROUP BY clause before the HAVING clause, or vice versa. Therefore the following two queries are the same and produce the same result:

SELECT CUST_NBR, COUNT(ORDER_NBR) 
FROM CUST_ORDER
GROUP BY CUST_NBR
HAVING COUNT(ORDER_NBR) > 1;

SELECT CUST_NBR, COUNT(ORDER_NBR) 
FROM CUST_ORDER
HAVING COUNT(ORDER_NBR) > 1
GROUP BY CUST_NBR;

  CUST_NBR COUNT(ORDER_NBR)
---------- ----------------
       231                6
       255                6

We can use a WHERE clause and a HAVING clause together in a query. When we do, it is important to understand the impact of the two clauses. Note that the WHERE clause is executed first, and the rows that don't satisfy the WHERE condition are not passed to the GROUP BY clause. The GROUP BY clause summarizes the filtered data into groups, and then the HAVING clause is applied to the groups to eliminate the groups that don't satisfy the HAVING condition. The following example illustrates this:

SELECT CUST_NBR, COUNT(ORDER_NBR) 
FROM CUST_ORDER
WHERE SALE_PRICE > 25
GROUP BY CUST_NBR
HAVING COUNT(ORDER_NBR) > 1;

  CUST_NBR COUNT(ORDER_NBR)
---------- ----------------
       231                4
       244                2
       264                2
       288                2

In this example, the WHERE clause first eliminates all the orders that don't satisfy the condition SALE_PRICE > 25. The rest of the rows are grouped on CUST_NBR. The HAVING clause eliminates the customers that don't have more than one order.