Kickstart 2020 with new opportunities! - hours only!Up to 80% off on all courses and bundles.-Close
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 damage_repair_cost (as sum_damage_repair_cost) 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 sum_damage_repair_cost.

Stuck? Here's a hint!

Use the following template:

SELECT
  ...
FROM ...
GROUP BY 
  CUBE (x, y, z)