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.

^{(explicit id gbcl002)
gbcl002}

GROUP BY Clause

GROUP BY

{ | [ table_object . ] column | ^[] { col_alias | select_number } }


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;

¹ Informix® extension