Suppose you have the following data in the sales_view table.
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 |
2 | 40625 | 53125 | 12500 | CA | Los Angeles | Long Beach |
To look at sales summaries by county and by city, use the following SELECT statement:
SELECT county, city, sum(margin) FROM sale_view GROUP BY GROUPING SETS ((county),(city));
The query reports the following data:
County City Sum(margin) ----------- ---------- ----------- Los Angeles ? 38700 San Diego ? 19500 ? Long Beach 24300 ? San Diego 19500 ? Avalon 14400
Notice that in this example, a null represents all values for a column because the column was excluded from the grouping set represented.
To distinguish between rows with nulls in actual data from rows with nulls generated from grouping sets, use the GROUPING function:
SELECT county, city, sum(margin), GROUPING(county) AS County_Grouping, GROUPING(city) AS City_Grouping FROM sale_view GROUP BY GROUPING SETS ((county),(city));
The results are:
County City Sum(margin) County_Grouping City_Grouping ----------- ---------- ----------- --------------- ------------- Los Angeles ? 38700 0 1 San Diego ? 19500 0 1 ? Long Beach 24300 1 0 ? San Diego 19500 1 0 ? Avalon 14400 1 0
You can also use GROUPING to replace the nulls that appear in a result row because the extended grouping specification aggregated over a column and excluded it from the particular grouping. For example:
SELECT CASE WHEN GROUPING(county) = 1 THEN '-All Counties-' ELSE county END AS County, CASE WHEN GROUPING(city) = 1 THEN '-All Cities-' ELSE city END AS City, SUM(margin) FROM sale_view GROUP BY GROUPING SETS (county,city);
The query reports the following data:
County City Sum(margin) -------------- ------------ ----------- Los Angeles -All Cities- 38700 San Diego -All Cities- 19500 -All Counties- Long Beach 24300 -All Counties- San Diego 19500 -All Counties- Avalon 14400