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 < 6;



  CUST_NBR COUNT(ORDER_NBR)

---------- ----------------

         1                8

         4                4

         5                6

Notice that the output only includes customers with numbers below 6. That's because the HAVING clause specified cust_nbr < 6 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 references only unsummarized data. It's more efficient to use WHERE cust_nbr < 6 instead of HAVING cust_nbr < 6, 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 < 6

GROUP BY cust_nbr;

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)

---------- ----------------

         1                8

         4                4

         5                6

See the use of the aggregate function COUNT 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 conditions you can write in the HAVING clause. A HAVING condition can refer only to an expression in the SELECT list, or to an expression involving an aggregate function. If you specify an expression in the HAVING clause that isn't in the SELECT list, or that isn't an aggregate expression, you 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

However, you can use an aggregate expression in the HAVING clause, even if it doesn't appear in the SELECT list, as illustrated in the following example:

SELECT cust_nbr

FROM cust_order

GROUP BY cust_nbr

HAVING COUNT(order_nbr) < 5;



  CUST_NBR

----------

         4

         8

In the preceding example, the HAVING clause refers to COUNT(order_nbr), which is not in the SELECT list. You are not required to show in your result set all the columns or expressions that determine which rows end up in that result set.

The order of the GROUP BY clause and the HAVING clause in a SELECT statement is not important. You 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) > 2;



SELECT cust_nbr, COUNT(order_nbr) 

FROM cust_order

HAVING COUNT(order_nbr) > 2

GROUP BY cust_nbr;



  CUST_NBR COUNT(ORDER_NBR)

---------- ----------------

         1                8

         4                4

         5                6

Even though Oracle doesn't care whether the HAVING clause comes before the GROUP BY clause or after, the HAVING clause is applied to the groups created by the GROUP BY clause, so it is a good programming practice to always put the HAVING clause after the GROUP BY clause. Another reason for placing HAVING after GROUP BY is that SQL Standard requires that particular order. Thus, putting HAVING after GROUP BY makes your code more portable.

You can use a WHERE clause and a HAVING clause together in a query. When you do, it is important to understand the impact of the two clauses. 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)

---------- ----------------

         1                4

         4                4

         8                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.