With Out USING Union We Can Get the SUM Of Rows At Group Level and Whole Records At Bottom

Hi All,


Most of the time we will use the UNION to get the whole sum of records group by some column and showing at each group level sum.

But in this Case we need to hit the database two times , then also  we cant get the whole Records sum, for that again we need to add one more UNION.

So to over come this situation i used the ROLLUP. 

SCRIPT:

CREATE TABLE [dbo].[SUMING_Data](
[ID] [int] NULL,
[Amount] [int] NULL,
[NAME] [varchar](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (1, 100, N'x')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (1, 200, N'y')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (1, 300, N'z')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (2, 200, N'p')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (4, 200, N'q')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (2, 400, N'r')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (4, 600, N's')
INSERT [dbo].[SUMING_Data] ([ID], [Amount], [NAME]) VALUES (3, 100, N't')

GO

SELECT * FROM [SUMING_Data]

in the above screen each color represent the different group name

But i want out put like each group level sum and all rows sum at the bottom.

for that previously i used UNION 

THIS IS USING UNION, HERE WE NEED TO WRITE TWO QUIRES


SELECT * FROM SUMING_Data
UNION
SELECT [ID],sum([Amount]),'Total' FROM SUMING_Data GROUP BY [ID]

Problem:

In the above screen shot red line showing the individual amount ,and blue one showing the Group Total 
But if i want all the records total , again i need to add one more UNION and one more hit to the database 

To overcome the above problem follow the below query.

Solution:

WITH OUT USING UNION ALSO WE CAN DO THIS ,USING THIS OUR QUERY IS VERY SIMPLE

Method 1:

SELECT  ID, SUM(AMOUNT) Amount, ISNULL(NAME,'TOTAL')GroupLevel  
FROM    SUMING_Data  
GROUP BY  ID,NAME 
WITH  ROLLUP

if we are using  isnull query we will get below out put:

Method 2:

SELECT ID, SUM(AMOUNT) Amount, GROUPING(name) GroupLevel 
FROM  SUMING_Data  
GROUP BY  ID,name 
WITH  ROLLUP

If we are using the Grouping we will get below out put ,here 0 indicates the individual amount and 1 indicates the SUM amount and at the bottom u can find the Total Amount in both Outputs

This is all about RollUP in SQL 

Thanks For Visiting Blog.