Introduction
CUBE
Summary

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 2n 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)