Add Total to the query

Question

Post reply

Add Total to the query

PSB

SSChampion

Points: 13195
More actions
December 18, 2013 at 5:38 am

#280456

Fi,
I have a table with the Group name and Total Count by group . I need to add a 'Total' and summation of all counts at the end .
CREATE TABLE #TempTable
(GroupName VARCHAR(10),NumberOfCases INT )
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp A',10)
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp B',20)
SELECT * from #TempTable
I want the result to be like the below :
Grp A 10
Grp B 20
Total 30
Thanks,
PSB

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

Not sure if I'm over-simplifying your problem but how about the below?!

INSERT INTO #TempTable (GroupName, NumberOfCases)

SELECT 'Total', SUM(NumberOfCases)

FROM #TempTable

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

HanShi SSC-Dedicated Points: 33506 More actions · Answer 2

If the solution from Abu Dina is oversimplified, below is a more practical solution.

SELECT GroupName, SUM(NumberOfCases) as NumberOfCases

FROM #TempTable

GROUP BY GroupName

UNION ALL

SELECT 'Grand Total' as GroupName, SUM(NumberOfCases)

FROM #TempTable

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

vignesh.ms Hall of Fame Points: 3456 More actions · Answer 3

PSB (12/18/2013)
Fi,
I have a table with the Group name and Total Count by group . I need to add a 'Total' and summation of all counts at the end .
CREATE TABLE #TempTable
(GroupName VARCHAR(10),NumberOfCases INT )
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp A',10)
INSERT INTO #Temp Table (GroupName,NumberOfCases)
VALUES ('Grp B',20)
SELECT * from #TempTable
I want the result to be like the below :
Grp A 10
Grp B 20
Total 30
Thanks,
PSB

Hope following will give you the better solution...

with cte as (

select groupname ,SUM(numberofcases)numberofcases from #TempTable group by groupname with rollup

)

select isnull (GROUPname , 'Total' )GROUPname ,numberofcases from cte

David Burrows SSC Guru Points: 65138 More actions · Answer 4

SELECT COALESCE(GroupName,'Total') AS [GroupName],SUM(NumberOfCases) AS [NumberOfCases]

FROM #TempTable

GROUP BY GroupName WITH ROLLUP

ORDER BY GROUPING(GroupName) ASC

Far away is close at hand in the images of elsewhere.
Anon.

Hunchback SSCrazy Points: 2649 More actions · Answer 5

You can also use the sub-clause GROUPING SETS to specifiy the groups. Use function GROUPING to sort the data as desired (Total at the end).

USE tempdb;

GO

SELECT

GroupName, NumberOfCases

INTO #Temp

FROM

(

VALUES

('Grp A',10),

('Grp B',20)

) AS T(GroupName,NumberOfCases);

SELECT

CASE WHEN GROUPING(GroupName) = 1 THEN 'Total' ELSE GroupName END as GroupName,

SUM(NumberOfCases) AS sum_NumberOfCases

FROM

#Temp

GROUP BY

GROUPING SETS (

(GroupName),

()

)

ORDER BY

GROUPING(GroupName),

GroupName;

DROP TABLE #Temp;