GROUP BY clause

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 ) 
| ( )

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;

A 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.

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

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.

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.

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

When using the GROUP BY clause, you can group by expressions (with some limitations), 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.

The empty GROUP BY list, (), signifies the treatment of the entire input as a single group. For example, the following two statements are equivalent:

SELECT COUNT(), SUM(Salary) FROM Employees;

SELECT COUNT(), SUM(Salary) FROM Employees GROUP BY ();

SQL/2008 GROUP BY is a core feature of the SQL/2008 standard. GROUPING SETS, GROUP BY (), ROLLUP, and CUBE constitute portions of optional SQL/2008 language feature T431. SQL Anywhere does not support optional SQL/2008 language feature T432, "Nested and concatenated GROUPING SETS".
Vendor extensions to the GROUP BY clause include:
- WITH ROLLUP
- WITH CUBE
- the ability to specify arbitrary expressions as GROUP BY terms. In the SQL/2008 standard, every GROUP BY term must be a column reference from an underlying table in the query's FROM clause.

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;

GROUP BY clause

Syntax

Parameters

Remarks

See also

Standards and compatibility

Examples