Cube and rollup functions [SQL Server]

For the sake of simplicity, suppose the following table:

Di1 Id2 myvalue

---------------------------

A x 2
A x 1
A y 2
A y 1
B x 3
B y 2
B y 2

Use the sum () function to aggregate the values of the third column:

 SELECT ID1,ID2,SUM(MyValue) 
FROM #Sample
GROUP BY ID1,ID2

Returned results:

Id1 Id2 (no column name)

A x 3
B x 3
A y 3
B y 4

Cube and rollup get results from the query of the group, and apply the same aggregate function to the values in the first column or each combination of all column values that appear in the group by column list.

Rollup Function

This is the easiest way to calculate the subtotal and total for the first column in the group by column list. In the hypothetical example, in addition to calculating the sum of each unique column value, you also need to calculate the sum of Rows A and B in column di1.

 SELECT ID1,ID2,SUM(MyValue)
FROM #Sample
GROUP BY ID1,ID2
    WITH ROLLUP

The result is as follows:

Id1 Id2 (no column name)

-------------------------

A x 3
A y 3
A null 6
B x 3
B y 4
B null 7
Null null 13

A null value indicates that related columns are ignored when the aggregate value is calculated.

Cube Functions

The cube operator is an extension of the rollup operator. Cube is not used to accumulate aggregate values for the 1st columns in the group by list, but to accumulate the column values of each group.

 SELECT ID1,ID2,SUM(MyValue)
FROM #Sample
GROUP BY ID1,ID2
    WITH CUBE

Result:

Id1 Id2 (no column name)

-------------------------

A x 3
B x 3
Null x 6
A y 3
B y 4
Null Y 7
Null null 13
A null 6
B null 7

The Null Value in column 1st indicates that the column value is the accumulation of the value in Column 2nd. These rows contain the subtotal of the rows where Id2 is equal to X or Y. The values of the two grouping columns are empty, indicating that the two columns are a total, that is, the sum of all rows.