GROUP BY Clause

Use the GROUP BY clause to produce a single row of results for each group. A group is a set of rows that have the same values for each column (or column expression) that is referenced in this clause.

This syntax fragment is part of the SELECT statement.
(1)
GROUP BY Clause

1  GROUP BY
1+ ,
1 ?  table_object . column
1 1
2.1 col_alias
2.1 select_number
Notes:
  • 1 HCL OneDB™ extension
Element Description Restrictions Syntax
col_alias Alias for a column name Must have been declared in the Projection clause Identifier
column Group rows by the value of this column (or of this expression) See Dependencies between the GROUP BY and Projection clauses. Identifier, Expression
select _number Integer specifying the ordinal position of a column or expression in the select list of the Projection clause See the Select numbers section. Literal Number
table_object Name, synonym, or alias of the table or view containing column Must exist and must be specified in the FROM clause Identifier

The SELECT statement with a GROUP BY clause returns a single row of results for each group of rows that have the same value in column, or that have the same value in the column that col_alias references, or that have the same value in the column or expression that the select_number specifies.

In an NLSCASE INSENSITIVE database, collation and string comparisons on NCHAR and NVARCHAR data disregard lettercase differences, so that the database server treats case variants among strings composed of same sequence letters as duplicates. For queries that group data on NCHAR or NVARCHAR columns, if some of the qualifying rows differ only in letter case, the number of groups will be smaller than from the same query on the same data set in a case-sensitive database. For more information on data processing in databases that were created with the NLSCASE INSENSITIVE property, see Duplicate rows in NLSCASE INSENSITIVE databases and NCHAR and NVARCHAR expressions in case-insensitive databases.

In a column listed in a GROUP BY clause, each row that contains a NULL value belongs to a single group. That is, all NULL values are grouped together.

Select numbers

You can use one or more integers in the GROUP BY clause to stand for column expressions. In the next example, the first SELECT statement uses select numbers for order_date and paid_date - order_date in the GROUP BY clause. You can group only by a combined expression using the select numbers.

In the second SELECT statement, you cannot replace the 2 with the arithmetic expression paid_date - order_date: 
SELECT order_date, COUNT(*), paid_date - order_date
   FROM orders GROUP BY 1, 3;
SELECT order_date, paid_date - order_date
   FROM orders GROUP BY order_date, 2;