WITH ROLLUP

ROLLUP operator is used to generate subtotals and totals. The ROLLUP generates a result set that shows aggregate for a hierarchy of values in the selected columns.

Without going in the theoretical details we are going to an example to understand the power of ROLLUP operators.

Step-1 [ Create a Table ]

CREATE TABLE #my_tempTab

           (Level   VARCHAR(50)  NOT NULL,

            sName   VARCHAR(50) NULL,

            Marks   INT         NULL)

Step-2 [ Now Insert data in it ]

INSERT INTO  #my_tempTab

             (Level,  sName,  Marks)

VALUES       ('A', 'Joydeep', 30),

             ('B', 'Tufan', 30), 

             ('C', 'Sangram', 30), 

             ('A', 'Sudip', 30), 

             ('B', 'Mayank', 30), 

             ('C', 'Tanay', 30), 

             ('A', 'Subrata', 30), 

             ('B', 'Saikat', 30), 

             ('C', 'Partha', 30), 

             ('A', 'Sukamal', 30)

Step-3 [ Now Use GROUP BY Clause ]

SELECT Level,  SUM(Marks)  Marks

FROM   #my_tempTab                              

GROUP BY Level

ORDER BY 1

Output:

 Level      Marks

 A          120

 B          90

 C          90

Step-4 [ Now we create a report with Individual Level SUM and with this we need Grand Total ]

SELECT ISNULL(Level, 'GRAND TOTAL') Level,SUM(Marks)  Marks

FROM   #my_tempTab                              

GROUP BY Level WITH ROLLUP

ORDER BY 1

Output:

Level                                     Marks

A                                             120

B                                             90

C                                             90

GRAND TOTAL                       300

So we understand that how the ROLLUP works.

Hope you like it.

Posted by: MR. JOYDEEP DAS