group-by-clause

 

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram             .-,-------------------.
             V                     |
>>-GROUP BY----grouping-expression-+---------------------------><
 

 

The GROUP BY clause specifies an intermediate result table that consists of a grouping of the rows of R. R is the result of the previous clause of the subselect.

A grouping-expression is an expression that defines the grouping of R. The following restrictions apply to grouping-expression.

The result of the GROUP BY clause is a set of groups of rows. In each group of more than one row, all values of each grouping-expression are equal, and all rows with the same set of values of the grouping-expressions are in the same group. For grouping, all null values for a grouping-expression are considered equal.

Because every row of a group contains the same value of any grouping-expression, grouping-expressions can be used in a search condition in a HAVING clause, in the SELECT clause, or in a sort-key-expression of an ORDER BY clause (see order-by-clause for details). In each case, the reference specifies only one value for each group. The grouping-expression specified in these clauses must exactly match the grouping-expression in the GROUP BY clause, except that blanks are not significant. For example, a grouping-expression of

SALARY*.10

will match the expression in a having-clause of

HAVING SALARY*.10

but will not match

HAVING .10 *SALARY
 or
HAVING (SALARY*.10)+100

If the grouping-expression contains varying-length strings with trailing blanks, the values in the group can differ in the number of trailing blanks and may not all have the same length. In that case, a reference to the grouping-expression still specifies only one value for each group, but the value for a group is chosen arbitrarily from the available set of values. Thus, the actual length of the result value is unpredictable.

The number of grouping-expressions must not exceed 120 and the sum of their length attributes must not exceed 32766-n bytes, where n is the number of grouping-expressions specified that allow nulls.

If a sort sequence other than *HEX is in effect when the statement that contains the GROUP BY clause is executed, and the grouping-expressions are SBCS data, mixed data, or Unicode data, then the rows are placed into groups using the weighted values. The weighted values are derived by applying the sort sequence to the grouping-expressions.


54.
If ALWCPYDTA(*NO) is specified, the length attribute must not be more than 2000, or 1999 if the expression is nullable.