HAVING clause

A HAVING clause restricts the results of a GROUP BY in a SelectExpression . 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

-- SELECT COUNT(*)
-- FROM SAMP.STAFF
-- GROUP BY ID
-- HAVING SALARY > 15000

The searchCondition, which is a specialized booleanExpression, can contain only grouping columns (see GROUP BY clause ), 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:

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.