T-SQL ROLLUP and CUBE

Introduction

First of all I salute all the new and rewarded MVPs in the month of April 2015. I congratulate them for their achievement. Hope the community can learn a lot of new things from them.

After my blog post on “Group By Grouping Set,” requests came from my friends circle to complete 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 are used for reporting purposes 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.

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

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

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.