ROLLUP operator is used to generate subtotals and grand total of the result set. The grand total and the subtotals are generated based on the columns included in the GROUP BY clause. Based on the order of columns specified in the GROUP BY clause, ROLLUP operator creates subtotals from right to left through the list of grouping columns and then finally creates a grand total.

Understanding ROLLUP

Consider a Movie table as shown below.

From the above table, if we want to summarize the data by purchase type and category, the query might look like this:

Now in the above result, if we also want to see total number of movies by purchase type, we might update the query to:

Now if we also want to see the overall total number of movies, we might update the query to:

As you can see, the code is not super readable and is unnecessarily too long. A better way of getting the same desired result is by using the ROLLUP operator as shown below.

In SQL, a cube is a data structure that has the capability of representing data across multiple dimensions. Just like ROLLUP operator, CUBE operator will generate subtotals and grand total of the result set based on the columns included in the GROUP BY clause, but instead of creating subtotals in a hierarchical dimension, CUBE creates subtotals for all possible combinations of columns. In short, CUBE should be used if subtotal needs to be in the result for more than one column.

Understanding CUBE

Consider a Movie table as shown below.

From the above table, if we want summarize the data by purchase type and category, the query might look like this:

Now in the above result, if we also want to see total number of movies by purchase type, we might update the query to:

Now, if we also want to see total number of movies by category in the above result, we might update the query to:

Now, if we also want add the grand total of movies to the result, we might update the query to:

An alternative way to get the same result is by using CUBE operator as shown below:

If you compare the above result with the result we got using ROLLUP operator, you would notice that by using CUBE operator, we are able to include subtotals for movie category as well.

As you can see, CUBE creates subtotals for all possible combinations. It is ideal to use CUBE when the columns represent multi-dimensional data. For example, if we need to calculate subtotals for combinations of product, year and state; CUBE would be an ideal choice. But in contrast using CUBE to calculate subtotals for day, month and year would be unnecessary for most analysis.