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