按月和年分组并进行汇总。
提问于 2015-05-19 05:06:38
我使用下面的代码列出该财政年度每个月的计算结果(Apr14到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)代码的运行是为了给我以下格式的每月数字：
Mth Yr n HES DataCompleteness
2014年4月49 48.5 101.03
我想在底部添加一个汇总总数，我通常会使用rollup进行汇总，但是因为我在多个字段上分组，所以它会向我的结果添加重复行。
有办法绕道吗？我正在使用Server 2012
谢谢
回答 2
Stack Overflow用户
回答已采纳
发布于 2015-05-19 05:10:37
按GROUPING SETS与空分组：
...
Group by Grouping Sets((datename(month,arvd), datepart(month,arvd), year(arvd)), ())
order by year(arvd), datepart(month,arvd)Stack Overflow用户
发布于 2015-05-27 07:10:39
谢谢关于分组集的建议，我已经将它们与子查询组合起来，以获得所需的输出：
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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30326531
复制相关文章
点击加载更多
腾讯云开发者
