GROUP BY GROUPING SETS

The GROUPING SETS clause is an extension to the GROUP BY clause of a SELECT statement. The GROUPING SETS clause allows you to group your results multiple ways, without having to use multiple SELECT statements to do so. This means you can reduce response time and improve performance.

For example, the following two queries statements are semantically equivalent. However, the second query defines the grouping criteria more efficiently using a GROUP BY GROUPING SETS clause.

Multiple groupings using multiple SELECT statements:



SELECT NULL, NULL, NULL, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
   UNION ALL
SELECT City, State, NULL, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY City, State
   UNION ALL
SELECT NULL, NULL, CompanyName, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY CompanyName;

Multiple groupings using GROUPING SETS:

SELECT City, State, CompanyName, COUNT( * ) AS Cnt
FROM Customers
WHERE State IN ( 'MB' , 'KS' )
GROUP BY GROUPING SETS( ( City, State ), ( CompanyName ) , ( ) );

Both methods produce the same results, shown below:

City State CompanyName Cnt
1 (NULL) (NULL) (NULL) 8
2 (NULL) (NULL) Cooper Inc. 1
3 (NULL) (NULL) Westend Dealers 1
4 (NULL) (NULL) Toto's Active Wear 1
5 (NULL) (NULL) North Land Trading 1
6 (NULL) (NULL) The Ultimate 1
7 (NULL) (NULL) Molly's 1
8 (NULL) (NULL) Overland Army Navy 1
9 (NULL) (NULL) Out of Town Sports 1
10 'Pembroke' 'MB' (NULL) 4
11 'Petersburg' 'KS' (NULL) 1
12 'Drayton' 'KS' (NULL) 3

Rows 2-9 are the rows generated by grouping over CompanyName, rows 10-12 are rows generated by grouping over the combination of City and State, and row 1 is the grand total represented by the empty grouping set, specified using a pair of matched parentheses (). The empty grouping set represents a single partition of all the rows in the input to the GROUP BY.

Notice how NULL values are used as placeholders for any expression that is not used in a grouping set, because the result sets must be combinable. For example, rows 2-9 result from the second grouping set in the query (CompanyName). Since that grouping set did not include City or State as expressions, for rows 2-9 the values for City and State contain the placeholder NULL, while the values in CompanyName contain the distinct values found in CompanyName.

Because NULLs as used as placeholders, it is easy to confuse placeholder NULLs with actual NULLs found in the data. To help distinguish placeholder NULLs from NULL data, use the GROUPING function. See Detecting placeholder NULLs using the GROUPING function.

 Example
 Specifying an empty grouping specification
 Specifying duplicate grouping sets
 Practicing good form