sql - 汇总总计

Question

按县和季度查询总计。尝试在底部添加所有县的总计以及所有县的季度总计。

添加了 rollup 的代码，但它没有返回任何东西，表格是一样的，没有总计

select  isnull(convert(VARCHAR,DCOUNTYNAME),'GRAND TOTAL') AS  County, 
    Year(DDATE) as Year, ---selects year value for total and quarter totals
    count(*) as Total, ---counts year totals
    sum(case when DATEPART(q, DDATE)=1 then 1 else 0 end) as Q1,
    sum(case when DATEPART(q, DDATE)=2 then 1 else 0 end) as Q2,
    sum(case when DATEPART(q, DDATE)=3 then 1 else 0 end) as Q3,
    sum(case when DATEPART(q, DDATE)=4 then 1 else 0 end) as Q4
into #tfinal
from Cert
where FLAG = 'o' and year(cast(ddDate as date)) ='2013' 
group by DCOUNTYNAME,
         Year(DDATE) with rollup

现在的结果

County      Year    Total   Q1  Q2  Q3  Q4
IMPERIAL    2013    129 5   20  100 4
FRESNO      2013    67  19  16  15  17
SAN DIEGO   2013    408 70  66  94  194

想要得到的结果

County      Year    Total   Q1  Q2  Q3  Q4
IMPERIAL    2013    129 5   20  100 4
FRESNO      2013    67  19  16  15  17
SAN DIEGO   2013    408 70  66  94  194
Grand Total         595 94 106 209  215

Answer 1

根据评论，听起来您可能已经获得了您想要的结果。这是另一种方法，此方法允许您明确控制总计的显示方式。

With CTE as (
--Insert Existing query without rollup here
)

Select * From CTE

Union All

Select 'Grand Total' as County
    , null as Year
    , SUM(Total) as Total
    , SUM(Q1) as Q1
    , SUM(Q2) as Q2
    , SUM(Q3) as Q3
    , SUM(Q4) as Q4

From CTE