Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

T-SQL ROLLUP and CUBE

DZone's Guide to

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
Free Resource

Learn how to move from MongoDB to Couchbase Server for consistent high performance in distributed environments at any scale.

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.

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.

Want to deliver a whole new level of customer experience? Learn how to make your move from MongoDB to Couchbase Server.

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 DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

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

X

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

{{ parent.tldr }}

{{ parent.urlSource.name }}