SQL Language Reference / Clauses |
Restrict the results of a GROUP BY in a SelectExpression.
HAVING searchCondition
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