求教，这个需求下，统计SQL如何写？

表:temp
id branch_name score level
1 成都 1 1
2 重庆 1 1
3 成都 2 2

统计结果：

level=1 level=2 合计
机构 [score 次数] [score 次数] [score 次数]

也就是说：统计在不同level下score的总分值和次数。

写来写去，都在绕圈子，求指教!单起来写，没啥问题，不知道咋合起来。

第 1 条附言 · 2013-06-04 14:41:21 +08:00

https://gist.github.com/eagleon/5704012

nbsp

score

level

4 条回复 • 1970-01-01 08:00:00 +08:00

lichao

2013-06-04 14:06:58 +08:00

select branch_name,

sum(case level when 1 then 1 else 0 end),
sum(case level when 2 then 1 else 0 end),

sum(case level when 1 then 1 else 0 end)
+
sum(case level when 2 then 1 else 0 end)

group by branch_name

lichao

2013-06-04 14:08:42 +08:00

select branch_name,

sum(case level when 1 then score else 0 end),
sum(case level when 1 then 1 else 0 end),

sum(case level when 2 then score else 0 end),
sum(case level when 2 then 1 else 0 end),

sum(case level when 1 then score else 0 end)
+
sum(case level when 2 then score else 0 end),

sum(case level when 1 then 1 else 0 end)
+
sum(case level when 2 then 1 else 0 end)

group by branch_name

zl8723

2013-06-04 14:17:02 +08:00

@lichao 谢谢，感谢已送出。

zl8723

2013-06-04 14:39:00 +08:00

select branch_name,
sum(case level when 1 then final_score else 0 end) z_s,
count(case level when 1 then final_score else null end) z_c,

sum(case level when 2 then final_score else 0 end) f_s,
count(case level when 2 then final_score else null end) f_c,

sum( final_score) t_s,
count(final_score) t_c
from temp
group by branch_name