GROUP BY clause

Use this clause to group columns, alias names, and functions as part of the SELECT statement.

Syntax

GROUP BY
| group-by-term, ... ]
| simple-group-by-term, ...  WITH ROLLUP
| simple-group-by-term, ... WITH CUBE
|GROUPING SETS ( group-by-term, ... )

group-by-term :
simple-group-by-term
| ( simple-group-by-term, ... )
| ROLLUP ( simple-group-by-term, ... )
| CUBE ( simple-group-by-term, ... )

simple-group-by-term :
expression
| ( expression ) 
| ( )

Parameters

GROUPING SETS clause The GROUPING SETS clause allows you to perform aggregate operations on multiple groupings from a single query specification. Each set specified in a GROUPING SET clause is equivalent to a GROUP BY clause.

For example, the following two queries are equivalent:

SELECT a, b, SUM( c ) FROM t 
GROUP BY GROUPING SETS ( ( a, b ), ( a ), ( b ), ( ) );

SELECT a, b, SUM( c ) FROM t 
  GROUP BY a, b 
UNION ALL
SELECT a, NULL, SUM( c ) FROM t 
  GROUP BY a 
UNION ALL
SELECT NULL, b, SUM( c ) FROM t 
  GROUP BY b 
UNION ALL
SELECT NULL, NULL, SUM( c ) FROM t;

An grouping expression may be reflected in the result set as a NULL value, depending on the grouping in which the result row belongs. This may cause confusion over whether the NULL is the result of another grouping, or whether the NULL is the result of an actual NULL value in the underlying data. To distinguish between NULL values present in the input data and NULL values inserted by the grouping operator, use the GROUPING function. See GROUPING function [Aggregate].

Specifying an empty set of parentheses ( ) in the GROUPING SETS clause returns a single row containing the overall aggregate.

For more information about using empty parentheses in GROUPING sets, including an example, see Specifying an empty grouping specification.

ROLLUP clause The ROLLUP clause is similar to the GROUPING SETS clause in that it can be used to specify multiple grouping specifications within a single query specification. A ROLLUP clause of n simple-group-by-terms generates n+1 grouping sets, formed by starting with the empty parentheses, and then appending successive group-by-terms from left to right.

For example, the following two statements are equivalent:

SELECT a, b, SUM( c ) FROM t 
GROUP BY ROLLUP ( a, b );

SELECT a, b, SUM( c ) FROM t 
GROUP BY GROUPING SETS ( ( a, b ), a, ( ) );

You can use a ROLLUP clause within a GROUPING SETS clause.

For more information about ROLLUP operations, see Using ROLLUP.

CUBE clause The CUBE clause is similar to the ROLLUP and GROUPING SETS clauses in that it can be used to specify multiple grouping specifications within a single query specification. The CUBE clause is used to represent all possible combinations that can be made from the expressions listed in the CUBE clause.

For example, the following two statements are equivalent:

SELECT a, b, SUM( c ) FROM t 
GROUP BY CUBE ( a, b, c );

SELECT a, b, SUM( c ) FROM t 
GROUP BY GROUPING SETS ( ( a, b, c ), ( a, b ), ( a, c ), 
 ( b, c ), a, b, c, () );

You can use a CUBE clause within a GROUPING SETS clause.

For more information about ROLLUP operations, see Using CUBE.

WITH ROLLUP clause This is an alternative syntax to the ROLLUP clause, and is provided for T-SQL compatibility.
WITH CUBE clause This is an alternate syntax to the CUBE clause, and is provided for T-SQL compatibility.

Remarks

When using the GROUP BY clause, you can group by columns, alias names, or functions. The result of the query contains one row for each distinct value (or set of values) of each grouping set.

Standards and compatibility

SQL/2003 While the GROUP BY clause is a core feature, GROUPING SETS, ROLLUP, and CUBE are features outside of core SQL. For example, the ROLLUP clause is part of feature T431. WITH ROLLUP and WITH CUBE are vendor extensions.

Examples

The following example returns a result set showing the total number of orders, and then provides subtotals for the number of orders in each year (2000 and 2001).

SELECT year ( OrderDate ) Year, Quarter ( OrderDate ) Quarter, count(*) Orders
FROM SalesOrders
GROUP BY ROLLUP ( Year, Quarter )
ORDER BY Year, Quarter;

Like the preceding ROLLUP operation example, the following CUBE query example returns a result set showing the total number of orders and provides subtotals for the number of orders in each year (2000 and 2001). Unlike ROLLUP, this query also gives subtotals for the number of orders in each quarter (1, 2, 3, and 4).

SELECT year (OrderDate) Year, Quarter ( OrderDate ) Quarter, count(*) Orders
FROM SalesOrders
GROUP BY CUBE ( Year, Quarter )
ORDER BY Year, Quarter;

The following example returns a result set that gives subtotals for the number of orders in the years 2000 and 2001. The GROUPING SETS operation lets you select the columns to be subtotaled instead of returning all combinations of subtotals like the CUBE operation.

SELECT year (OrderDate) Year, Quarter ( OrderDate ) Quarter, count(*) Orders 
FROM SalesOrders
GROUP BY GROUPING SETS ( ( Year, Quarter ), ( Year ) )
ORDER BY Year, Quarter;