Relationship of GROUP BY and Projection Clauses

A GROUP BY clause restricts what the Projection clause can specify. If you use a GROUP BY clause, each column specified in the select list of the Projection clause must also be included in the GROUP BY clause.

If you use an aggregate function and one or more column expressions in the select list, you must put all the column names that are not used as part of an aggregate or time expression in the GROUP BY clause.

Constant expressions and BYTE or TEXT column expressions are not valid in the GROUP BY list.

If the select list includes a BYTE or TEXT column, you cannot use the GROUP BY clause. In addition, you cannot include a ROWID in a GROUP BY clause.

In Dynamic Server, if your select list includes a column of a user-defined data type, the column cannot be used in a GROUP BY clause unless the UDT can use the built-in bit-hashing function. Any UDT that cannot use the built-in bit-hashing function must be created with the CANNOTHASH modifier, which tells the database server that the UDT cannot be used in a GROUP BY clause.

The following example names one column that is not in an aggregate expression. The total_price column should not be in the GROUP BY list because it appears as the argument of an aggregate function. The COUNT and SUM aggregates are applied to each group, not to the whole query set.

SELECT order_num, COUNT(*), SUM(total_price)
   FROM items GROUP BY order_num 

If a column stands alone in a column expression in the select list, you must use it in the GROUP BY clause. If a column is combined with another column by an arithmetic operator, you can choose to group by the individual columns or by the combined expression using the number.