4.4.8 HAVING clause

Organization of this subsection
(1) Format
(2) Function
(3) Operands
(4) Syntax rules
(5) Notes
(6) Usage example

(1) Format

HAVING-clause::=HAVING[Figure]search-condition

(2) Function

Specifies a search condition for a relation to be returned by the FROM clause, WHERE clause, or GROUP BY clause. The HAVING clause causes a logical operation to be performed based on the specified search condition and only returns the data that meets the condition as a relation.

When the HAVING clause is specified, the relation returned by the HAVING clause becomes the target of the SELECT clause.

(3) Operands

search-condition

For details about specifying a search condition, see 4.4.17 Search condition.

To use a column specification in a search condition, specify either grouped columns or specify a column as an argument of an aggregate function. For details about grouped columns, see 4.4.7 GROUP BY clause, and for details about aggregate functions, see 4.4.12 Aggregate functions.

(4) Syntax rules

Selects groups for which the search condition specified in the HAVING clause is true.

(5) Notes

If you omit the HAVING clause, all groups in the relation resulting from the preceding GROUP BY or FROM clause are selected.

(6) Usage example

Groups columns b and c of relation s1 and outputs the data in columns a, b, and c when the average value of column a is greater than 10. The underlined part indicates the HAVING clause.

REGISTER QUERY q1 SELECT AVG(s1.a) AS a1,s1.b,s1.c FROM s1[ROWS 100]
                 GROUP BY s1.b,s1.c HAVING AVG(s1.a) > 10;