HAVING Clause

Use the HAVING clause to apply one or more qualifying conditions to groups or to the entire result set.

This syntax fragment is part of the SELECT statement.


HAVING Clause

                         (1)   
|--HAVING--| Condition |----------------------------------------|

Notes:

See Condition

In the following examples, each condition compares one calculated property of the group with another calculated property of the group or with a constant. The first SELECT statement uses a HAVING clause that compares the calculated expression COUNT(*) with the constant 2. The query returns the average total price per item on all orders that have more than two items.

The second SELECT statement lists customers and the call months for customers who have made two or more calls in the same month:

SELECT order_num, AVG(total_price) FROM items
   GROUP BY order_num HAVING COUNT(*) > 2; 
SELECT customer_num, EXTEND (call_dtime, MONTH TO MONTH) 
   FROM cust_calls GROUP BY 1, 2 HAVING COUNT(*) > 1;

You can use the HAVING clause to place conditions on the GROUP BY column values as well as on calculated values. This example returns cust_code and customer_num, call_dtime, and groups them by call_code for all calls that have been received from customers with customer_num less than 120:

SELECT customer_num, EXTEND (call_dtime), call_code 
   FROM cust_calls GROUP BY call_code, 2, 1
   HAVING customer_num < 120;

The HAVING clause generally complements a GROUP BY clause. If you omit the GROUP BY clause, the HAVING clause applies to all rows that satisfy the query, and all rows in the table make up a single group. The following example returns the average price of all the values in the table, as long as more than ten rows are in the table:

SELECT AVG(total_price) FROM items HAVING COUNT(*) > 10;

Because conditions in the WHERE clause cannot include aggregate expressions, you can use the HAVING clause to apply conditions with aggregates to the entire result set of a query, as in the example above.

The condition in the HAVING clause cannot include a DISTINCT or UNIQUE aggregate expression. For example, the following query fails with a syntax error:

 SELECT order_num, COUNT(*) number, AVG (total_price) average
    FROM items
    GROUP BY order_num
       HAVING COUNT(DISTINCT *) > 2;

No error is issued, however, if the DISTINCT keyword is omitted from the example above.