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

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

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.

Get comfortable using NoSQL in a free, self-directed learning course provided by RavenDB. Learn to create fully-functional real-world programs on NoSQL Databases. Register today.

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

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}