grouping sets\aggregation and totals SQL Server 2008r2 1

[wbodger]

I am trying to aggregate data that includes some calculated columns and I am running into a bit of a roadblock. I had this all working until I was asked to add the two calculated columns AbCaseCount and AbControlCount. What I have is

; with cte as
(
SELECT distinct s.StaffID, sp.SiteID, st.SiteName, s.LastName, s.FirstName
from newcompass.dbo.tblStaffProtocol as sp join newcompass.dbo.tblStaff as s
    on s.StaffID = sp.StaffID  join newcompass.dbo.tlkpRole as r
    on sp.RoleIDInProtocol = r.RoleID  join newcompass.dbo.tblSite as st 
    on sp.SiteID = st.SiteID
where st.Active = '1' 
and s.Active = '1' 
and sp.Active = '1' 
and sp.ProjectID = '3' 
and sp.SiteID in (select distinct SiteID from newcompass.dbo.tlkpProtocol where ProtocolID='331' and SiteJobs like '%recruit%')
and sp.ProtocolID = '0' 
and r.LeadershipLevel = '1'
),

cte2 as
(
select SiteID, SiteName, LastName, FirstName, 
       (select count(PPT_CAHX_DXBREASTCAYN_CODE) from dbedrn331.dbo.tblOutcomes where PPT_CAHX_DXBREASTCAYN_CODE=1 and DELETEFLAG=0 and STUDY_SITE_ID=cte.SiteID) as AbCasecount,
       ((coalesce(COUNT(p.study_site_id),0))-(select count(PPT_CAHX_DXBREASTCAYN_CODE) from dbedrn331.dbo.tblOutcomes where PPT_CAHX_DXBREASTCAYN_CODE=1 and DELETEFLAG=0 and STUDY_SITE_ID=cte.SiteID)) as abcontrolcount,
    coalesce(COUNT(p.study_site_id),0) as sitetotal
from cte left outer join dbedrn331.dbo.tblParticipant p
on cte.SiteID=p.STUDY_SITE_ID 
AND p.DELETEFLAG=0 and p.ENTRYFLAG=1
group by SiteID, SiteName, FirstName, LastName
)

select case
        when siteid <> '' then convert(varchar(3),SiteID)+' - '+ SiteName+'/'+ FirstName +' ' + LastName
        else 'Total'
       end as site,
       AbCasecount,
       abcontrolcount,
       sitetotal
from cte2
group by GROUPING sets((SiteID, SiteName, FirstName, LastName,AbCasecount),(abcontrolcount),(sitetotal))

which does not give me what I am looking for, this gives me

Total	NULL	NULL	0
Total	NULL	NULL	19
Total	NULL	NULL	93
Total	NULL	0	NULL
Total	NULL	4	NULL
Total	NULL	63	NULL
163 - Northwestern University, Feinberg School of Medicine (Assoc. Member)/Seema Khan	30	NULL	NULL
709 - Geisinger Health System (Assoc. Member)/Azadeh Stark	15	NULL	NULL
756 - Henry Ford Hospital (Non-EDRN Site)/Dhananjay (Dan) Chitale	0	NULL	NULL

However, this code

; with cte as
(
SELECT distinct s.StaffID, sp.SiteID, st.SiteName, s.LastName, s.FirstName
from newcompass.dbo.tblStaffProtocol as sp join newcompass.dbo.tblStaff as s
    on s.StaffID = sp.StaffID  join newcompass.dbo.tlkpRole as r
    on sp.RoleIDInProtocol = r.RoleID  join newcompass.dbo.tblSite as st 
    on sp.SiteID = st.SiteID
where st.Active = '1' 
and s.Active = '1' 
and sp.Active = '1' 
and sp.ProjectID = '3' 
and sp.SiteID in (select distinct SiteID from newcompass.dbo.tlkpProtocol where ProtocolID='331' and SiteJobs like '%recruit%')
and sp.ProtocolID = '0' 
and r.LeadershipLevel = '1'
),

cte2 as
(
select SiteID, SiteName, LastName, FirstName, 
       (select count(PPT_CAHX_DXBREASTCAYN_CODE) from dbedrn331.dbo.tblOutcomes where PPT_CAHX_DXBREASTCAYN_CODE=1 and DELETEFLAG=0 and STUDY_SITE_ID=cte.SiteID) as AbCasecount,
       ((coalesce(COUNT(p.study_site_id),0))-(select count(PPT_CAHX_DXBREASTCAYN_CODE) from dbedrn331.dbo.tblOutcomes where PPT_CAHX_DXBREASTCAYN_CODE=1 and DELETEFLAG=0 and STUDY_SITE_ID=cte.SiteID)) as abcontrolcount,
    coalesce(COUNT(p.study_site_id),0) as sitetotal
from cte left outer join dbedrn331.dbo.tblParticipant p
on cte.SiteID=p.STUDY_SITE_ID 
AND p.DELETEFLAG=0 and p.ENTRYFLAG=1
group by SiteID, SiteName, FirstName, LastName
)

select case
        when siteid <> '' then convert(varchar(3),SiteID)+' - '+ SiteName+'/'+ FirstName +' ' + LastName
        else 'Total'
       end as site,
       AbCasecount,
       abcontrolcount,
       sitetotal
from cte2
compute sum(abcasecount),sum(abcontrolcount),sum(sitetotal)
[code]
gives me what  I am looking for
[code]
163 - Northwestern University, Feinberg School of Medicine (Assoc. Member)/Seema Khan	30	63	93
709 - Geisinger Health System (Assoc. Member)/Azadeh Stark	15	4	19
756 - Henry Ford Hospital (Non-EDRN Site)/Dhananjay (Dan) Chitale	0	0	0
[code]
and then the next recordset
[code]
45	67	112

But then again, this syntax is being deprecated (probably not even available in 2012?) So, how, within the queries themselves, can I get totals for AbCaseCount, AbControlCount and sitetotal rolled up by site and then just one line with those three totals? Any thoughts, suggestions, help?

Thanks,
wb

 

[imex]

Try:

group by GROUPING sets((SiteID, SiteName, FirstName, LastName, AbCasecount, abcontrolcount, sitetotal), ())

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[wbodger]

Actually, that had been the first thing that I tried, but

; with cte as
(
SELECT distinct s.StaffID, sp.SiteID, st.SiteName, s.LastName, s.FirstName
from newcompass.dbo.tblStaffProtocol as sp join newcompass.dbo.tblStaff as s
    on s.StaffID = sp.StaffID  join newcompass.dbo.tlkpRole as r
    on sp.RoleIDInProtocol = r.RoleID  join newcompass.dbo.tblSite as st 
    on sp.SiteID = st.SiteID
where st.Active = '1' 
and s.Active = '1' 
and sp.Active = '1' 
and sp.ProjectID = '3' 
and sp.SiteID in (select distinct SiteID from newcompass.dbo.tlkpProtocol where ProtocolID='331' and SiteJobs like '%recruit%')
and sp.ProtocolID = '0' 
and r.LeadershipLevel = '1'
),

cte2 as
(
select SiteID, SiteName, LastName, FirstName, 
       (select count(PPT_CAHX_DXBREASTCAYN_CODE) from dbedrn331.dbo.tblOutcomes where PPT_CAHX_DXBREASTCAYN_CODE=1 and DELETEFLAG=0 and STUDY_SITE_ID=cte.SiteID) as AbCasecount,
       ((coalesce(COUNT(p.study_site_id),0))-(select count(PPT_CAHX_DXBREASTCAYN_CODE) from dbedrn331.dbo.tblOutcomes where PPT_CAHX_DXBREASTCAYN_CODE=1 and DELETEFLAG=0 and STUDY_SITE_ID=cte.SiteID)) as abcontrolcount,
    coalesce(COUNT(p.study_site_id),0) as sitetotal
from cte left outer join dbedrn331.dbo.tblParticipant p
on cte.SiteID=p.STUDY_SITE_ID 
AND p.DELETEFLAG=0 and p.ENTRYFLAG=1
group by SiteID, SiteName, FirstName, LastName
)

select case
        when siteid <> '' then convert(varchar(3),SiteID)+' - '+ SiteName+'/'+ FirstName +' ' + LastName
        else 'Total'
       end as site,
       AbCasecount,
       abcontrolcount,
       sitetotal
from cte2
group by GROUPING sets((SiteID, SiteName, FirstName, LastName, AbCasecount, abcontrolcount, sitetotal), ())

gives me

163 - Northwestern University, Feinberg School of Medicine (Assoc. Member)/Seema Khan	30	63	93
709 - Geisinger Health System (Assoc. Member)/Azadeh Stark	16	5	21
756 - Henry Ford Hospital (Non-EDRN Site)/Dhananjay (Dan) Chitale	0	0	0
Total	NULL	NULL	NULL

So it gives me the row 'Total', but null values for the three columns that I want to sum individually.

Thanks,
wb

 

[imex]

Try:

select case
        when siteid <> '' then convert(varchar(3),SiteID)+' - '+ SiteName+'/'+ FirstName +' ' + LastName
        else 'Total'
       end as site,
       sum(AbCasecount) as AbCasecount,
       sum(abcontrolcount) as abcontrolcount,
       sum(sitetotal) as sitetotal
from cte2
group by GROUPING sets((SiteID, SiteName, FirstName, LastName, AbCasecount, abcontrolcount, sitetotal), ())

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[wbodger]

Ah... That did it, thanks!