T-SQL ROLLUP and CUBE
CUBE generates a result set that shows aggregates for all combinations of values and ROLLUP generates a result set.
Join the DZone community and get the full member experience.
Join For FreeIntroduction
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.
Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments