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 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:
-- 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.
-- Find the total number of economy seats taken on a flight, -- grouped by airline, -- only when the group has at least 2 records. SELECT SUM(ECONOMY_SEATS_TAKEN), AIRLINE_FULL FROM FLIGHTAVAILABILITY, AIRLINES WHERE SUBSTR(FLIGHTAVAILABILITY.FLIGHT_ID, 1, 2) = AIRLINE GROUP BY AIRLINE_FULL HAVING COUNT(*) > 1
Table of Contents