In our previous lesson we covered WITH ROLLUP and WITH CUBE. During that lesson you may have noticed that when we saw the summary rows, the columns had NULL as the value. Hopefully you wondered to yourself, how do I know programatically which of the rows are summary lines and which are rows that just had NULL for the value of that column?
Well that’s where GROUPING() comes in. With GROUPING(), you get a 1 where the row is a summary row, and 0 if it isn’t.
Let’s look at our previous WITH CUBE query.
SELECT Type, Store, SUM(Number) as Number FROM items GROUP BY type,store WITH CUBE
We got the results:
| Type | Store | Number |
| Shield | Tatooine | 18 |
| Shield | Korriban | 9 |
| Shield | NULL | 27 |
| Blaster | Tatooine | 12 |
| Blaster | Korriban | 5 |
| Blaster | Dantooine | 14 |
| Blaster | NULL | 31 |
| Lightsaber Crystal | Korriban | 1 |
| Lightsaber Crystal | Dantooine | 4 |
| Lightsaber Crystal | NULL | 5 |
| NULL | NULL | 63 |
| NULL | Tatooine | 30 |
| NULL | Korriban | 15 |
| NULL | Dantooine | 18 |
In this case all of those NULLs are summary lines, so if we changed the query to:
SELECT
CASE WHEN Type IS NOT NULL THEN Type ELSE
CASE WHEN GROUPING(Type) = 1 THEN
CASE WHEN GROUPING(store) = 0 'Subtotal Type' ELSE 'Grand Total' END
ELSE
'N/A'
END
END as Type
, CASE WHEN Store IS NOT NULL THEN Type ELSE
CASE WHEN GROUPING(Store) = 1 THEN
CASE WHEN GROUPING(Type) = 0 'Subtotal Store' ELSE 'Grand Total' END
ELSE
'N/A'
END
END as Store
, SUM(Number) as Number
FROM items
GROUP BY type,store
WITH CUBEWe’d get a bit nicer output.
| Type | Store | Number |
| Shield | Tatooine | 18 |
| Shield | Korriban | 9 |
| Shield | Subtotal Store | 27 |
| Blaster | Tatooine | 12 |
| Blaster | Korriban | 5 |
| Blaster | Dantooine | 14 |
| Blaster | Subtotal Store | 31 |
| Lightsaber Crystal | Korriban | 1 |
| Lightsaber Crystal | Dantooine | 4 |
| Lightsaber Crystal | Subtotal Store | 5 |
| Grand Total | Grand Total | 63 |
| Subtotal Type | Tatooine | 30 |
| Subtotal Type | Korriban | 15 |
| Subtotal Type | Dantooine | 18 |
Or, at the very least we’d have a result that tells us exactly what we’re looking at on each row.
Have any questions? Let me know! I’m here to help.
