Introduction
CUBE
Instruction
As you could see, the following rows were created:
GROUP BY CUBE(Location, Gender, Risk) = GROUP BY Location, Gender, Risk + GROUP BY Location, Gender + GROUP BY Gender, Risk + GROUP BY Location, Risk + GROUP BY Location + GROUP BY Gender + GROUP BY Risk + GROUP BY ()
Quite a lot! For n columns passed in as parameters, CUBE creates 2^n grouping levels, while ROLLUP only creates n + 1 levels:
| No. columns | No. grouping levels | |
|---|---|---|
| ROLLUP | CUBE | |
| 1 | 2 | 2 |
| 2 | 3 | 4 |
| 3 | 4 | 8 |
| 4 | 5 | 16 |
| 5 | 6 | 32 |
| 6 | 7 | 64 |
You need to be aware that CUBE can significantly lower the performance of your queries. As few as three columns in CUBE create eight different types of groupings. Even though a query with CUBE is faster than separate grouping queries merged with UNION, performance can still be an issue for large tables.
Exercise
Show the sum of DamageRepairCost (as SumDamageRepairCost)for all possible grouping combinations based on the Year, Month, and Cause columns.
Show the following columns in the query result: Year, Month, Cause, and SumDamageRepairCost.
Stuck? Here's a hint!
Use the following template:
SELECT ... FROM ... GROUP BY CUBE (x, y, z)
