HAVING

HAVING searchCondition

Description

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.

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

SELECT AVG (QTY), CID FROM TRADE.PORTFOLIO GROUP BY CID HAVING AVAILQTY > 150

-- Find the average QTY held by a customer grouped on Customer ID,
-- only when the customer holds atleast two different stocks
SELECT AVG (QTY), CID FROM TRADE.PORTFOLIO GROUP BY CID HAVING COUNT(*) > 2