Purpose
- In a single dimension without performing a full ROLLUP.
- In multiple dimensions without performing a full CUBE.
Syntax
Syntax Elements
- ordinary_grouping_set
- One or more expressions used to group cubed report rows across multiple dimensions.
- empty_grouping_set
- A contiguous LEFT PARENTHESIS, RIGHT PARENTHESIS pair with no argument. This syntax is used to provide a grand total.
- rollup_list
- A standard grouping expression set specified with ROLLUP. If you specify more than one expression, the expression list must be enclosed by parentheses.
- cube_list
- a standard grouping expression set specified with CUBE. If you specify more than one expression, the expression list must be enclosed by parentheses.
- grouping_sets_specification
- Any of the following:
- ordinary_grouping_setenclosed in parenthesis
- rollup_list
- cube_list
- empty_grouping_set
- grouping_sets_specification
ANSI Compliance
GROUPING SETS is ANSI SQL:2011 compliant.
SAMPLE Clause and GROUPING SETS
You cannot specify a SAMPLE clause in a query that also specifies the GROUPING SETS option in a GROUP BY clause. The exception is a query where the GROUPING SETS option is specified in a derived table or a view, and the SAMPLE clause is specified in the outer query.
How GROUPING SETS Summarizes Data
GROUPING SETS allows you to group your results set multiple times. Empty parentheses () specify a single grand total group that sums all the groups specified in the query.
Example: GROUPING SETS
The following example query forms the first group by state, county then forms the second group by city then forms the third group consisting of the whole table.
Suppose you have the following sales_view table data:
sales_view | ||||||
---|---|---|---|---|---|---|
PID | cost | sale | margin | state | county | city |
1 | 38350 | 50150 | 11800 | CA | Los Angeles | Long Beach |
1 | 63375 | 82875 | 19500 | CA | San Diego | San Diego |
1 | 46800 | 61200 | 14400 | CA | Los Angeles | Avalon |
1 | 40625 | 53125 | 12500 | CA | Los Angeles | Long Beach |
You are interested in looking at sales summaries by county within state, by city, and by state.
The following SELECT statement analyzes the data in sales_view for this information:
SELECT state, county, city, SUM(margin) FROM sales_view GROUP BY GROUPING SETS ((state,county),(city),());
The query reports the following results, where the QUESTION MARK character indicates a null:
state |
county |
city |
SUM(margin) |
----- |
------ |
---- |
----------- |
CA |
Los Angeles |
? |
38700 |
CA |
San Diego |
? |
19500 |
? |
? |
Long Beach |
24300 |
? |
? |
San Diego |
19500 |
? |
? |
Avalon |
14400 |
? |
? |
? |
58200 |
Notice that nulls are used to represent the empty set in this answer set, not missing information. The nulls mean that information is not reported at the grouping level represented, not that the information is missing from the sales_view base table. This usage is similar to the way nulls are used in outer joins to indicate empty sets of nonmatching rows.