HAVING clause restricts the results of a
HAVING clause is applied to each group of the grouped table,
similarly to how 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 expression cannot refer
directly to any column that does not have a
GROUP BY clause. It can,
however, refer to constants, aggregates, and special registers.
A specialized Boolean expression, as described in the next section.
The searchCondition, is a specialized booleanExpression that can contain only;
- grouping columns (see
- columns that are part of aggregate expressions
- columns that are part of a subquery
For example, the following query is illegal, because the column
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
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;