T-SQL ROLLUP and CUBE

Introduction

First of all I salute all the new and reward MVP in the month of April 2015. Congratulating them for their achievement. Hope the community can learn a lot of new thing from them.

After my blog related to “Group By Grouping Set” request came from my friends circle to completing the article by providing something related to CUBE, ROLLUP and COMPUTE. So in this article we are trying to learn something related to it. Hope it will be informative.

CUBE and ROLLUP

Generally CUBE and ROLLUP is used in reporting purpose and they do the Subtotal and Grand total. CUBE generates a result set that shows aggregates for all combinations of values in the selected columns and ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Let’s take an Example to understand it

We have a table like this.

Class	Section	Roll	Marks
1	A	1	40
1	A	2	30
1	A	3	20
1	B	1	40
1	B	2	30
1	B	3	30
2	A	1	20
2	A	2	60
2	A	3	40
2	B	1	20
2	B	2	30
2	B	3	20

If we make the WITH ROLLUP we can get the Output like this

Class	Section	Marks
1	A	90
1	B	100
1	NULL	190	90 + 100
2	A	120
2	B	70
2	NULL	190	120 + 70
NULL	NULL	380	190 + 190

If we make the WITH CUBE we can get the Output like this

Class	Section	Marks
1	A	90
1	B	100
1	NULL	190	90 + 100
2	A	120
2	B	70
2	NULL	190	120 + 70
NULL	NULL	380	190 + 190
NULL	A	210	90   +  120
NULL	B	170	100 +  70

Let’s take a practical example

Step- 1 [ Create Base Table ]

IF OBJECT_ID(N'[dbo].[tbl_EXAMPLETABLE]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EXAMPLETABLE];

   END

GO

CREATE TABLE [dbo].[tbl_EXAMPLETABLE]

    (

        CLASS        INT       NOT NULL,

        SECTION      CHAR(1)   NOT NULL,

        ROLL         INT       NOT NULL,

        MARKS        INT       NOT NULL

   );   

Step-2 [ Insert Records in Base Table ]

INSERT INTO [dbo].[tbl_EXAMPLETABLE]

    (CLASS, SECTION, ROLL, MARKS)

VALUES(1,  'A', 1, 40),

      (1,  'A',   2, 30),

      (1,  'A',   3, 20),

      (1,  'B',   1, 40),

      (1,  'B',   2, 30),

      (1,  'B',   3, 30),

      (2,  'A',   1, 20),

      (2,  'A',   2, 60),

      (2,  'A',   3, 40),

      (2,  'B',   1, 20),

      (2,  'B',   2, 30),

      (2,  'B',   3, 20);

Step – 4 [ Make the WITH ROLLUP ]

SELECT   CLASS, SECTION, SUM(MARKS) AS MARKS

FROM     [dbo].[tbl_EXAMPLETABLE]

GROUP BY CLASS, SECTION WITH ROLLUP;

Output

Step-5 [ Make the WITH CUBE  ]

SELECT   CLASS, SECTION, SUM(MARKS) AS MARKS

FROM     [dbo].[tbl_EXAMPLETABLE]

GROUP BY CLASS, SECTION WITH CUBE;

Output

Hope you like it.

Posted by: MR. JOYDEEP DAS