WITH ROLLUP and WITH CUBE Operators in SQL Server(TSQL)

We use the WITH ROLLUP and WITH CUBE operators with the GROUP BY statement. So before you read this article, I suggest you read the following article.

“Group By Clause, SUM, MAX, MIN, AVG, COUNT and COUNT_BIG Functions in SQL Server”

With Group By Clause, we can group a dataset into groups.

ROLLUP and CUBE Operators:

We can create subtotal and grandtotal rows for the grouped data using the ROLLUP and CUBE operators.

ROLLUP and CUBE operators generates aggregated results for the grouped columns by GROUP BY.

Difference Between ROLLUP and CUBE:

ROLLUP operator generates aggregated results according to the hierarchy of values in the selected columns. It produces sub total and grand total for grouped columns.

CUBE operator generates aggregated results for all combinations of values in the selected columns.

CUBE produces sub total and grand total too. The difference is that the CUBE operator creates a subtotal for all combinations of values.

In addition, ROLLUP groups the columns in the GROUP BY statement from right to left.

Let’s make an example for a better understanding of ROLLUP and CUBE operators.

Example:

First, we create a table with the help of the following script and add a few records into this table.

In the following script;

In the first stage, we execute GROUP BY Clause without WITH ROLLUP or WITH CUBE operators.

We group the Table by ProductName and ProductCode.

As you can see, there are 3 different groups. There are 2 records with the value of ProductName “Television”. A single group occurred because the ID values of these rows are the same.

There are 2 records with the value of ProductName “Phone” . Two groups occurred because the ID values of these rows are different.

In the second stage, we execute GROUP BY Clause using the WITH ROLLUP operator. As you can see, it grouped the columns from right to left. It also calculated the subtotal for each group. (Columns that appear as NULL)

In the last row, it calculated grandtotal.

In the third stage, we execute GROUP BY Clause using the WITH CUBE operator. As you can see, it calculated a subtotal for all combinations of values.

ROLLUP had 1 subtotal for the phone value. As you can see, CUBE has 2 subtotals  for the phone value.

In the last 3 rows, it calculated grandtotal for all values and subtotal for groups.

When you use the WITH ROLLUP and WITH CUBE operators, as you can see in the screenshot above, aggregated columns appears null. But because the queried data set has NULL values, some columns may be NULL. We use the GROUPING function to separate these two NULL types. Using the GROUPING function, we can ensure that the results are more meaningful when we use the ROLLUP and CUBE operators.

You can find detailed information in the following article.

“GROUPING and GROUPING_ID Functions in SQL Server(TSQL)”

After this article, I recommend that you read the article about the GROUPING SET operator that comes with SQL Server 2008 and works more flexible and performance than the ROLLUP and CUBE operators. In the article about the GROUPING SETS operator, there are many examples that describe the GROUPING SETS, ROLLUP and CUBE operators. I would like to thank Ahmet KAYMAZ for this.

“GROUPING SETS Operator in SQL SERVER(TSQL)”