CODEWITHSUNDEEP
sqlgroup-bysql-server-2012
GullitsMullet
GullitsMullet

Reputation: 358

Sql group by month and year with rollup

I'm using the code below to list a calculated output for each month of the financial year (Apr14 to Mar15): 

select 
datename(month,arvd) Mth,
year(arvd) Yr,
count(submissionid) n,
max(HES/12) HES,
cast(count(submissionid) / max(HES/12)*100 as decimal(5,2)) DataCompleteness

from PRIcache p
left join (select distinct hsite, HES from hsite where hyear=2013) hs on
hs.hSite = p.site

where siteid=8 and arvd between '20140401' and '20150331'

group by datename(month,arvd), datepart(month,arvd), year(arvd)
order by year(arvd), datepart(month,arvd)

The code is running to give me the monthly figures in the format below:

Mth Yr n HES DataCompleteness

April 2014 49 48.5 101.03

I want to add a summary total at the bottom, I'd usually do this with rollup but because I am grouping on more than one field it adds duplicates rows to my results.

Is there a way around this? I'm using SQL Server MS 2012

Thanks

Upvotes: 1

Views: 2470

Answers (2)

GullitsMullet
GullitsMullet

Reputation: 358

Thanks for the advice on Grouping Sets, I've combined them with a subquery to get the output I needed:

Select  isnull(Mth,'Total') Mth,
    isnull(Yr,'9999') Yr,
    sum(n) ActualSubmissions,
    sum(HES) ExpectedSubmissions,
    cast(avg(DataCompleteness) as decimal(5,2)) DataCompleteness

from 

    (select     datename(month,arvd) Mth,
                Month(arvd) MthSort,
                Year(arvd) Yr,
                count(submissionid) n,
                HES/12 HES,
                cast(count(submissionID)/(HES/12) as decimal(5,2))   DataCompleteness
     from PRIcache p
        left join hSite on p.site=hSite and hyear=2013
     where siteid=8888 and arvd between '20140401' and '20150330'
     group by datename(month,arvd), Month(arvd), year(arvd), HES) #datacomp 


group by Grouping sets ((Mth,Yr,MthSort),())

order by Yr, MthSort

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Group by GROUPING SETS with empty grouping:

...
Group by Grouping Sets((datename(month,arvd), datepart(month,arvd), year(arvd)), ())
order by year(arvd), datepart(month,arvd)

Upvotes: 1

Related Questions