一个分组的问题,求教,急
小弟要实现的效果就是分类汇总该编号成功和失败的总数,这是我的错误代码,求教
select   id,   (select   count(check1)   as   check1   from   tbl_a   where   check1= 'pass '   and   check2= 'pass ')   pass     ,(   select   count(check2)   as   check2   from   tbl_a   where   check2= 'failed '   and   check2= 'failed ')   failed   from   tbl_a   group   by   id,   order   by   id   desc

要实现的结果
id     pass     failed
4         5             0
3         2             3

表中的记录
id   check1   check2
4       pass     pass    
4       pass     pass    
4       pass     pass    
4       pass     pass    
4       pass     pass    
3       pass     pass    
3       pass     pass    
3       failed   failed
3       failed   failed
3       failed   failed


------解决方案--------------------
錯了...

---------

select id, sum(case check1 when 'pass ' then 1 else 0 end) as pass
sum(case check2 when 'failed ' then 1 else 0 end) as failed
from table
group by id
------解决方案--------------------
應該是兩個條件都滿足才統計。

echiynn(寶琲)，你的還是寫少了條件。 :)
------解决方案--------------------
select id, sum(case when check1= 'pass ' and check2= 'pass ' then 1 else 0 end) as pass ,sum(case when check1= 'failed ' and check2= 'failed ' then 1 else 0 end) as failed from tbl_a group by id order by id desc