1.13 Aggregating retrieved data by group (GROUP BY clause, HAVING clause)

Use the GROUP BY clause to aggregate retrieved data by group. In the examples of the GROUP BY clause shown below, the sales history table (SALESLIST) is used.

Example:

The following determines the sum of the quantities purchased for each product code (PUR-CODE) in the sales history table (SALESLIST).

SELECT statement specification

  SELECT "PUR-CODE",SUM("PUR-NUM")
      FROM "SALESLIST"
      GROUP BY "PUR-CODE"

[Figure]

Retrieval results

[Figure]

The specification format of the GROUP BY clause and HAVING clause is as follows.

Specification format

SELECT "column-name" FROM "table-name"
    WHERE search-condition
    GROUP BY "column-name"
    HAVING search-condition

GROUP BY "column-name":

Specify the column by which the retrieved data is aggregated. For example, the following will aggregate the retrieved data by product code (PUR-CODE).

Example: GROUP BY "PUR-CODE"

HAVING search-condition:

You can specify search conditions to narrow down the retrieved data that was aggregated by groups in the GROUP BY clause. For a specification example, see 1.13.4 Example 4: Determine the quantity purchased for each product code (narrow down retrieval by specifying a HAVING clause).

Note

You can also specify a grouping specification that is not a column name in the GROUP BY clause. For details about the syntax of the GROUP BY clause and HAVING clause, see the following.

GROUP BY clause: 7.7.1 Specification format and rules for GROUP BY clauses
HAVING clause: 7.8.1 Specification format and rules for HAVING clauses

Organization of this section

To Page Top