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.

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