A HAVING clause restricts the results of a GROUP BY in a . The HAVING clause is applied
to each group of the grouped table, much as a WHERE clause is applied to a
select list. If there is no GROUP BY clause, the HAVING clause is applied
to the entire result as a single group. The SELECT clause cannot refer directly
to any column that does not have a GROUP BY clause. It can, however, refer
to constants, aggregates, and special registers.
Syntax
HAVING searchCondition
The searchCondition, which is a specialized booleanExpression, can contain only grouping columns (see ), columns that are part of aggregate expressions,
and columns that are part of a subquery. For example, the following query
is illegal, because the column SALARY is not a grouping column, it does not
appear within an aggregate, and it is not within a subquery:
-- SELECT COUNT(*)
-- FROM SAMP.STAFF
-- GROUP BY ID
-- HAVING SALARY > 15000
Aggregates in the HAVING clause do not need to appear in the SELECT list.
If the HAVING clause contains a subquery, the subquery can refer to the outer
query block if and only if it refers to a grouping column.