Over a million developers have joined DZone.

T-SQL ROLLUP and CUBE

CUBE generates a result set that shows aggregates for all combinations of values and ROLLUP generates a result set.

· Database Zone

Learn NoSQL for free with hands-on sample code, example queries, tutorials, and more.  Brought to you in partnership with Couchbase.

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.

ClassSection RollMarks
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

ClassSection Marks
1 A 90
1 B 100
1NULL19090 + 100
2 A 120
2 B 70
2NULL190120 + 70
NULLNULL380190 + 190

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

ClassSection Marks
1 A 90
1 B 100
1NULL19090 + 100
2 A 120
2 B 70
2NULL190120 + 70
NULLNULL380190 + 190
NULLA21090  +  120
NULLB170100 +  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.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
sql ,microsoft ,sql server ,rollup ,cube

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}