Monday, 6 April 2015

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

1 comment:

  1. The strongest and fearless person is the one who knows his/her mistakes and still seek for guidance and forgiveness. Have a pleasant day and keep on smiling. Visit my site for more information.

    triciajoy.com

    www.triciajoy.com

    ReplyDelete