4.4.7 GROUP BY clause

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

(1) Format

GROUP-BY-clause::=GROUP[Figure]BY[Figure]column-specification-list

(2) Function

Specifies the columns (grouped columns) in the relation returned by the previously specified clause. Grouping is performed on the specified grouped columns only.

In grouping, rows with the same value in the column specified by the GROUP BY clause are grouped together and output as a row.

(3) Operands

column-specification-list

For details about specifying a column specification list, see 4.4.13 Column specification list.

When specifying columns in the column specification list, you cannot specify the same column name more than once. If the preceding FROM clause references only one relation, you only need to specify column names in the column specification list. If the FROM clause references multiple relations, you must specify a data identifier and a column name for each column in the column specification list to make the column names unique.

(4) Syntax rules

None.

(5) Notes

If the GROUP BY clause is specified in a relation expression, the only columns you can specify in the select expression in the SELECT clause are elements (grouped column names) specified by the GROUP BY clause, or a value expression that starts with one of these column names.

(6) Usage example

Groups columns b and c of relation s1, sums the values in column a, and outputs the data in columns b and c. The underlined part indicates the GROUP BY clause.

REGISTER QUERY q1 SELECT SUM(s1.a) AS sum_a, s1.b, s1.c
                 FROM s1[ROWS 100] GROUP BY s1.b,s1.c;