SQL-J Language Reference
Page 35 of 121

HAVING clause

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.

Syntax

HAVING searchCondition

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 C2 is not a grouping column, it does not appear within an aggregate, and it is not within a subquery:

-- SELECT COUNT(*)
-- FROM t
-- GROUP BY c1
-- HAVING c2 > 1

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.

Example

-- 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 FlightAvailability.flight_id.substring(0,2).equals(
    airline)
GROUP BY airline_full
HAVING COUNT(*) > 1

-- find the average temperature of cities in a region for February
-- where that average is above 75
SELECT AVG (city.showTheTemperature(DATE'1998-02-01', 'F')),
    region
FROM Cities JOIN Countries USING (country_ISO_code)
GROUP BY region
HAVING AVG (city.showTheTemperature(
    DATE'1998-02-01', 'F')) > 75.0