select a.城市,b.城市汇总,a.类型, 正确=sum(case when a.结果='正确' then 票数 else 0 end), 错误=sum(case when a.结果='错误' then 票数 else 0 end), 总计=sum(a.票数), 达标率=ltrim(cast(sum(case when a.结果='正确' then a.票数 else 0 end)*1.0/sum(a.票数)*100 as numeric(18,2)))+'%' from ZS_kpi a, (select 城市,sum(票数) as 城市汇总 from ZS_kpi group by 城市)b where a.城市=b.城市 group by a.城市,a.类型
select a.城市,b.总计数量 as 城市汇总,a.类型,a.正确,a.错误,a.总计,a.达标率 from( select 城市,类型, 正确=sum(case when 结果='正确' then 票数 else 0 end), 错误=sum(case when 结果='错误' then 票数 else 0 end), 总计=sum(票数),达标率=ltrim(cast(sum(case when 结果='正确' then 票数 else 0 end)*1.0/sum(票数)*100 as numeric(18,2)))+'%' from ZS_kpi group by 城市,类型 )a inner join [城市-汇总] b on a.城市=b.城市
select a.城市,b.总计数量 as 城市汇总,a.类型,a.正确,a.错误,a.总计,a.达标率 from( select 城市,类型, 正确=sum(case when 结果='正确' then 票数 else 0 end), 错误=sum(case when 结果='错误' then 票数 else 0 end), 总计=sum(票数),达标率=ltrim(cast(sum(case when 结果='正确' then 票数 else 0 end)*1.0/sum(票数)*100 as numeric(18,2)))+'%' from ZS_kpi group by 城市,类型 )a inner join (select 城市,sum(票数) as 总计数量 from zs_kpi group by 城市)b on a.城市=b.城市
a.城市,b.城市汇总,a.类型,
正确=sum(case when a.结果='正确' then 票数 else 0 end),
错误=sum(case when a.结果='错误' then 票数 else 0 end),
总计=sum(a.票数),
达标率=ltrim(cast(sum(case when a.结果='正确' then a.票数 else 0 end)*1.0/sum(a.票数)*100 as numeric(18,2)))+'%'
from
ZS_kpi a,
(select 城市,sum(票数) as 城市汇总 from ZS_kpi group by 城市)b
where
a.城市=b.城市
group by a.城市,a.类型
from(
select 城市,类型,
正确=sum(case when 结果='正确' then 票数 else 0 end),
错误=sum(case when 结果='错误' then 票数 else 0 end),
总计=sum(票数),达标率=ltrim(cast(sum(case when 结果='正确' then 票数 else 0 end)*1.0/sum(票数)*100 as numeric(18,2)))+'%'
from ZS_kpi group by 城市,类型
)a inner join [城市-汇总] b on a.城市=b.城市
from(
select 城市,类型,
正确=sum(case when 结果='正确' then 票数 else 0 end),
错误=sum(case when 结果='错误' then 票数 else 0 end),
总计=sum(票数),达标率=ltrim(cast(sum(case when 结果='正确' then 票数 else 0 end)*1.0/sum(票数)*100 as numeric(18,2)))+'%'
from ZS_kpi group by 城市,类型
)a inner join (select 城市,sum(票数) as 总计数量 from zs_kpi group by 城市)b on a.城市=b.城市