group-by-clause

             .-,-------------------.   
             V                     |   
>>-GROUP BY----grouping-expression-+---------------------------><

grouping-expression

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

• If grouping-expression is a single column, the column name must unambiguously identify a column of R.
• The result of grouping-expression cannot be a LOB data type (or a distinct type that is based on a LOB) or an XML data type.
• grouping-expression cannot include any of the following items:
  • A correlated column
  • A host variable
  • An aggregate function
  • Any function or expression that is not deterministic or that is defined to have an external action
  • A scalar fullselect
  • A CASE expression whose searched-when-clause contains a quantified predicate, an IN predicate using a fullselect, or an EXISTS predicate

The result of GROUP BY 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-expression are in the same group. For grouping, all null values for a grouping-expression are considered equal.

If a grouping-expression contains DECFLOAT values, the DECFLOAT values with the same value will be in the same group. But the number of digits returned for each group is unpredictable.

Because every row of a group contains the same value of any grouping-expression, a grouping-expression can be used in a search condition in a HAVING clause or an expression in a SELECT clause, or in a sort-key-expression of an ORDER BY clause. In each case, the reference specifies only one value for each group. For example, if grouping-expression is col1+col2, col1+col2+3 would be an allowed expression in the select list. Associative rules for expressions do not allow the similar expression of 3+col1+col2, unless parentheses are used to ensure that the corresponding expression is evaluated in the same order. Thus, 3+(col1+col2) would also be allowed in the select list. If the concatenation operator is used, grouping-expression must be used exactly as the expression was specified in the select list.

If a grouping-expression contains varying-length strings with trailing blanks, the values in the group can differ in the number of trailing blanks and might not all have the same length. In that case, a reference to 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.

Row access controls do not affect the operation of the GROUP BY clause.

In certain contexts, the semantics of the column mask can conflict with those in the GROUP BY clause. When this occurs, the column mask cannot be applied for the statement and an error will be returned at bind time.