HAVING Clause
Use the HAVING clause to apply one or more qualifying conditions to groups or to the entire result set.
- 1 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.
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;
SELECT customer_num, EXTEND (call_dtime), call_code FROM cust_calls GROUP BY call_code, 2, 1 HAVING customer_num < 120;
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.
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.