7.8.1 Specification format and rules for HAVING clauses

(1) Specification format

HAVING-clause ::= HAVING search-condition

To Page Top

(2) Explanation of specification format

search-condition:

For details about search conditions, see 7.18 Search conditions.

To Page Top

(3) Rules

1. Each column specification in search-condition must meet one of the following conditions:

   • It specifies a grouping column name.

     Example 1:

     SELECT COUNT("C2") FROM "T1" GROUP BY "C1" HAVING "C1">100

     For the underlined portions, the same column name (grouping column name) must be specified.

     Example 2:

     SELECT "GC1",COUNT(*) FROM "MEMBERS" 
         GROUP BY CASE WHEN "AGE">=90 THEN 90 ELSE TRUNC("AGE",-1) END AS "GC1"
         HAVING "GC1">=20

     For the underlined portions, the same column name (grouping column name) must be specified.

   • It specifies the same value expression as the value expression included in a grouping specification (value expression that includes a column specification).

     Example:

     SELECT "C1"+"C2" FROM "T1" GROUP BY "C1"+"C2" HAVING "C1"+"C2" > 100

     The same value expression (value expression including a column specification) must be specified for the underlined items.

   • It specifies the argument to a set function.

     Example:

     SELECT COUNT("C2") FROM "T1" HAVING MAX("C1")>100

     The underlined portion indicates the argument to the set function.

   • It specifies an external reference column.

     Example:

     SELECT * FROM "T1"
         WHERE EXISTS(SELECT * FROM "T2" HAVING MAX("C1")<"T1"."C1")

     The underlined portion indicates the external reference column.

2. Each column specification that is contained in any subqueries of search-condition and that references a table reference column specified in the preceding FROM clause must meet the following conditions:

   • It specifies the column specification included alone in the preceding GROUP BY clause (regardless of whether AS column-name is specified in grouping-specification).

     Example:

     SELECT "C1" FROM "T1"
         GROUP BY "C1"
         HAVING EXISTS(SELECT * FROM "T2" WHERE "C1"<"T1"."C1")

     For the underlined portion, the same column specification that is included alone in the grouping column in the preceding GROUP BY clause must be specified.

   • It specifies the argument to a set function.

     Example:

     SELECT COUNT("C1") FROM "T1"
         HAVING EXISTS(SELECT * FROM "T2" WHERE "C1"<MAX("T1"."C1"))

     The underlined portion indicates the set function in which the external reference column is specified as an argument.

3. The search conditions specified in the HAVING clause are applied to the results of the GROUP BY clause. For details about the order in which the results of the table expression are derived, see (3) Rules in 7.4.1 Specification format and rules for table expressions.

To Page Top

(4) Examples

Example 1

Using the data in the sales history table (SALESLIST), this example determines the sum and average of the quantities purchased on or after September 3, 2011 by product code (PUR-CODE).

Furthermore, retrieve only the product code groups for which the sum of the quantities purchased is 20 or fewer.

SELECT "PUR-CODE",SUM("PUR-NUM") AS "SUM",AVG("PUR-NUM") AS "AVG"
    FROM "SALESLIST"
        WHERE "PUR-DATE">=DATE'2011-09-03'
        GROUP BY "PUR-CODE"
        HAVING SUM("PUR-NUM")<=20

The underlined portion indicates the HAVING clause.

Example of execution results

Example 2

This example selects the departments (SCODE) that have an average member age that is less than the average age of all employees, and obtains the average member age for each of those departments.

SELECT "SCODE",AVG("AGE") AS "AVG"
    FROM "EMPLIST"
       GROUP BY "SCODE"
       HAVING AVG("AGE")<(SELECT AVG("AGE") FROM "EMPLIST")

The underlined portion indicates the HAVING clause.

Example of execution results

To Page Top