关于汇总的事

[Ryo_Hazuki]

我想把汇总后年度的那个NULL用∑替换，请问该如何做呢
use northwind
select year(birthdate) as '年度', avg(cast(extension as int)) as '範囲' from employees group by year(birthdate) with rollup

[ReViSion]

--试下这个
select case grouping(year(birthdate)) when 1 then '∑' else year(birthdate) as '年度' end, avg(cast(extension as int)) as '範囲' from employees
group by year(birthdate) with rollup

[Ryo_Hazuki]

可以不用子查询的方法吗

[Ryo_Hazuki]

不行啊
服务器: 消息 156，级别 15，状态 1，行 1
在关键字 'as' 附近有语法错误。

我如果是
select case when(grouping(year(birthdate)) = 1) then '∑' end as '年度', avg(cast(extension as int)) as '範囲' from employees group by year(birthdate) with rollup

汇总的那个∑可以出但分类的年度都显示为NULL了

如果写成
select case when(grouping(year(birthdate)) = 1) then '∑' else isnull(year(birthdate), 'N/A') end as '年度', avg(cast(extension as int)) as '範囲' from employees group by year(birthdate) with rollup

则抛错
服务器: 消息 8120，级别 16，状态 1，行 1
列 'employees.BirthDate' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。

我该怎么办

[duoluohuifeng]

关注

[lengxiaowei]

select '年度'=case when grouping(birthdate)=1 then '∑' else cast(birthdate as varchar) end,
avg(cast(extension as int)) as '範囲' from
(select birthdate=year(birthdate),extension from employees) a
group by birthdate with rollup