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.
-- 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