Difference between ROLLUP and CUBE

We can understand the difference between ROLLUP and CUBE with a simple example. Consider we have a table which contains the results of quarterly test of students. In certain cases we need to see the total corresponding to the quarter as well as the students. Here is the sample table

SELECT * INTO #TEMP

FROM

(

    SELECT 'Quarter 1' PERIOD,'Amar' NAME ,97 MARKS

    UNION ALL

    SELECT 'Quarter 1','Ram',88

    UNION ALL

    SELECT 'Quarter 1','Simi',76

    UNION ALL

    SELECT 'Quarter 2','Amar',94

    UNION ALL

    SELECT 'Quarter 2','Ram',82

    UNION ALL

    SELECT 'Quarter 2','Simi',71

    UNION ALL

    SELECT 'Quarter 3' ,'Amar',95

    UNION ALL

    SELECT 'Quarter 3','Ram',83

    UNION ALL

    SELECT 'Quarter 3','Simi',77

    UNION ALL

    SELECT 'Quarter 4' ,'Amar',91

    UNION ALL

    SELECT 'Quarter 4','Ram',84

    UNION ALL

    SELECT 'Quarter 4','Simi',79

)TAB

1. ROLLUP(Can find total for corresponding to one column)

(a) Get total score of each student in all quarters.

SELECT * FROM #TEMP

UNION ALL

SELECT PERIOD,NAME,SUM(MARKS) TOTAL

FROM #TEMP

GROUP BY NAME,PERIOD

WITH ROLLUP

HAVING PERIOD IS NULL AND NAME IS NOT NULL

// Having is used inorder to emit a row that is the total of all totals of each student

Following is the result of (a)

(b) Incase you need to get total score of each quarter

SELECT * FROM #TEMP

UNION ALL

SELECT PERIOD,NAME,SUM(MARKS) TOTAL

FROM #TEMP

GROUP BY PERIOD,NAME

WITH ROLLUP

HAVING PERIOD IS NOT NULL AND NAME IS NULL

Following is the result of (b)

2. CUBE(Find total for Quarter as well as students in a single shot)

SELECT PERIOD,NAME,SUM(MARKS) TOTAL

FROM #TEMP

GROUP BY NAME,PERIOD

WITH CUBE

HAVING PERIOD IS NOT NULL OR NAME IS NOT NULL

Following is the result of CUBE

Now you may be wondering about the real time use of ROLLUP and CUBE. Sometimes we need a report in which we need to see the total of each quarter and total of each student in a single shot. Here is an example

I am changing the above CUBE query slightly as we need total for both totals.

SELECT CASE WHEN PERIOD IS NULL THEN 'TOTAL' ELSE PERIOD END PERIOD,

CASE WHEN NAME IS NULL THEN 'TOTAL' ELSE NAME END NAME,

SUM(MARKS) MARKS

INTO #TEMP2

FROM #TEMP

GROUP BY NAME,PERIOD

WITH CUBE

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + PERIOD + ']',

               '[' + PERIOD + ']')

               FROM    (SELECT DISTINCT PERIOD FROM #TEMP2) PV 

               ORDER BY PERIOD   

DECLARE @query NVARCHAR(MAX)

SET @query = 'SELECT * FROM

             (

                 SELECT * FROM #TEMP2

             ) x

             PIVOT

             (

                 SUM(MARKS)

                 FOR [PERIOD] IN (' + @cols + ')

            ) p;'

EXEC SP_EXECUTESQL @query

Now you will get the following result