Dependencies between the GROUP BY and Projection clauses

The GROUP BY clause restricts what the Projection clause can specify. If you include a GROUP BY clause, each column in the select list of the Projection clause must also be referenced in the GROUP BY clause. Some column data types and column expressions that are valid in the Projection clause, however, cannot be referenced in the GROUP BY clause.

If a column expression in the select list is only the column name or alias, you must use its name or its alias in the GROUP BY clause, if none of the restrictions below apply to the column. If a column is combined with another column by an arithmetic operator, you can choose to group the query result set in either of two ways:
  • By the names or aliases of the individual columns,
  • or else by the combined expression using the select number, a literal integer that specifies the ordinal position of that expression within the select list of the Projection clause.
The following restrictions, however, prevent some columns and expressions from being included in the GROUP BY clause, or can prevent the query from including the GROUP BY clause.

  • Constant expressions are not valid in the GROUP BY clause.

  • You cannot include a ROWID in a GROUP BY clause.

  • If the Projection clause includes a BYTE or TEXT column or a BYTE or TEXT column expression, the query cannot include the GROUP BY clause.

  • If the Projection clause 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 section identifies restrictions on the GROUP BY clause when the Projection clause includes aggregate expressions or time expressions.

Columns in aggregate expressions and time expressions

If you specify an aggregate function and one or more column expressions in the select list of a query that includes the GROUP BY clause, the GROUP BY clause must list the name or the alias of each column in the select list that is not used as part of an aggregate or of a time expression. The query fails with error -321, however, if the GROUP BY clause includes a select number corresponding to the ordinal position of an aggregate expression within the select list.

If an OLAP window function is specified in the Projection clause of a query that includes the GROUP BY clause, all column references within the OLAP window function must also be included in the GROUP BY clause. The database server issues an error, however, if any of the columns referenced in the GROUP BY clause are operands of an aggregate expression or of a time expression.

The GROUP BY clause in the following example specifies one column that is not in an aggregate expression. Here the total_price column must not be in the GROUP BY list, because it appears in the Projection clause as the argument to an aggregate function. 
SELECT order_num, COUNT(*), SUM(total_price)
   FROM items GROUP BY order_num;
During execution of this query, the COUNT and SUM aggregates are applied to each order_num group, rather than calculating the number of orders and the sum of their total_price values across the set of all rows in the items table.