overall sum for multiple columns

[wbodger]

I am running 2008 r2. I have a query for which i need to do several sums

select	fg.siteid+' '+ s.abbreviation as site,
		isnull(joinedheds,0) AS 'JoinedHEDS', 
		isnull(hedsabstracted,0) AS 'HEDSAbstracted', 
		sumOfFinalGroup2-isnull(joinedheds,0)-isnull(hedsabstracted,0) as 'HEDSNeither', 
		isnull(casesabstracted,0) AS 'CASESAbstracted', 
		sumOfFinalGroup1-isnull(casesabstracted,0) as 'CASESNeither',
		isnull(sumOfFinalGroup1,0)+isnull(sumOfFinalGroup2,0) AS 'DCPTotals'
from (select left(study_participant_id,3) as siteid from dbDCP.dbo.tblFinalG) as fg left join newcompass.dbo.tblsite s on fg.siteid = s.SiteID
	left join ( 
		select COUNT(*) as sumOfFinalGroup2, LEFT(study_participant_id,3) as siteid 
		from dbDCP.dbo.tblFinalG 
		where FINAL_GROUP = 2 and DELETEFLAG=0
		group by LEFT(study_participant_id,3) 
		) as sumOfFinalGroup2
	on fg.siteid=sumOfFinalGroup2.siteid
	left join (
		select COUNT(*) as sumOfFinalGroup1 , LEFT(study_participant_id,3) as siteid 
		from dbDCP.dbo.tblFinalG 
		where FINAL_GROUP = 1 and DELETEFLAG=0
		group by LEFT(study_participant_id,3) 
		) as sumOfFinalGroup1
	on fg.siteid=sumOfFinalGroup1.siteid
	left join (select COUNT(*) as joinedheds, STUDY_SITE_ID from dbEDRN316.dbo.tblVConsent group by STUDY_SITE_ID) as vconsent on sumOfFinalGroup2.siteid=vconsent.STUDY_SITE_ID
	left join (select COUNT(*) as hedsabstracted, STUDY_SITE_ID from dbEDRN316.dbo.tblVDAControl group by STUDY_SITE_ID) as vdacontrol on sumOfFinalGroup2.siteid=vdacontrol.STUDY_SITE_ID
	left join (select COUNT(*) as casesabstracted, STUDY_SITE_ID from dbEDRN316.dbo.tblVDACase group by STUDY_SITE_ID) as vdacase on sumOfFinalGroup1.siteid=vdacase.STUDY_SITE_ID
group by fg.siteid+' '+ s.abbreviation, joinedheds, hedsabstracted, casesabstracted, sumOfFinalGroup1, sumOfFinalGroup2
compute sum(isnull(joinedheds,0)), sum(isnull(hedsabstracted,0)), sum(sumOfFinalGroup2-isnull(joinedheds,0)-isnull(hedsabstracted,0)), sum(isnull(casesabstracted,0)), sum(sumOfFinalGroup1-isnull(casesabstracted,0)), sum(isnull(sumOfFinalGroup1,0)+isnull(sumOfFinalGroup2,0))

works, and gives me

143 UMICH	4	1	97	0	88	190	764
144 Mt. Sinai	0	0	99	0	112	211	0
145 Mayo Clinic	0	0	31	0	63	94	0
146 St. Louis University	0	0	57	0	46	103	0
147 Stanford	0	0	71	0	62	133	0
148 UPENN	0	0	52	0	40	92	0
275 MAYO-JACK	0	0	26	0	22	48	0

4	1	433	0	433	871

but I know compute is deprecated and I should not use it. So, what is the proper way to get those sums at the bottom?

wb

 

[genomon]

Might be some help here:

http://blog.sqlauthority.com/2012/04/02/sql-server-use-roll-up-clause-instead-of-compute-by/

Beir bua agus beannacht!

 

[wbodger]

I have looked at Rollup, but from what I have found in my testing and in the article it replaces the compute by, not the compute clause. My biggest issue, it seems, is that I need both detail and aggregate data.

wb