Both WITH ROLLUP and WITH CUBE clause permit us to execute
multiple "levels" of aggregation in a single statement. CUBE
generates a result set which shows aggregates for all combinations of values in
the selected columns whereas ROLLUP generates a result set which shows
aggregates for a hierarchy of values in the selected columns.
-- Create table tbl_stu
CREATE TABLE [dbo].[tbl_stu](
[id] [int]
IDENTITY(1,1) PRIMARY KEY,
[rollno] [int] NULL,
[stu_name] [nvarchar](50) NULL,
[subj] [nvarchar](20) NULL,
[marks] [int] NULL
)
GO
-- Insert data
into table
INSERT [dbo].[tbl_stu] ([id], [rollno], [stu_name], [subj], [marks]) VALUES (1, 1, N'Ricky', N'Phy', 65)
GO
INSERT [dbo].[tbl_stu] ([id], [rollno], [stu_name], [subj], [marks]) VALUES (2, 2, N'Sam', N'Math', 70)
GO
INSERT [dbo].[tbl_stu] ([id], [rollno], [stu_name], [subj], [marks]) VALUES (3, 3, N'Peter', N'Che', 55)
GO
INSERT [dbo].[tbl_stu] ([id], [rollno], [stu_name], [subj], [marks]) VALUES (4, 1, N'Ricky', N'Math', 60)
GO
INSERT [dbo].[tbl_stu] ([id], [rollno], [stu_name], [subj], [marks]) VALUES (5, 1, N'Ricky', N'Che', 50)
GO
INSERT [dbo].[tbl_stu] ([id], [rollno], [stu_name], [subj], [marks]) VALUES (6, 2, N'Sam', N'Phy', 72)
GO
INSERT [dbo].[tbl_stu] ([id], [rollno], [stu_name], [subj], [marks]) VALUES (7, 3, N'Peter', N'Phy', 62)
GO
INSERT [dbo].[tbl_stu] ([id], [rollno], [stu_name], [subj], [marks]) VALUES (8, 2, N'Sam', N'Che', 50)
GO
INSERT [dbo].[tbl_stu] ([id], [rollno], [stu_name], [subj], [marks]) VALUES (9, 3, N'Peter', N'Math', 68)
GO
-- SELECT Records
from the table
SELECT * FROM tbl_stu
SELECT * FROM tbl_stu
Sometimes we need a report in
which we need to see Subject-wise total and total of each student in a single
shot. Here is an example using WITH ROLLUP and WITH CUBE clause.
-- WITH ROLLUP
SELECT [stu_name]
,[subj]
,Sum([marks])
FROM [test].[dbo].[tbl_stu] GROUP BY stu_name, subj WITH ROLLUP
-- WITH CUBE
SELECT [stu_name]
,[subj]
,Sum([marks])
FROM [test].[dbo].[tbl_stu] GROUP BY stu_name, subj WITH CUBE
