一个分组的问题,求教,急

[klj218]

小弟要实现的效果就是分类汇总该编号成功和失败的总数,这是我的错误代码,求教
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

[echiynn]

select id, sum(case check1 when 'pass' then 1 else 0 end) as pass
sum(case check2 when 'pass' then 1 else 0 end) as failed
from table
group by id

[echiynn]

錯了...

---------

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

[paoluo]

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

[paoluo]

應該是兩個條件都滿足才統計。

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

[昵称被占用了]

check1='pass' and check2='pass' 的算pass
check1='failed' and check2='failed' 的算failed
还有其他情况就没在统计范围了

[echiynn]

嘿嘿,沒看清...

[klj218]

学习,结帖

[paoluo]

唉，一樣的語句，咋就沒有Haiwer(海阔天空) 的值錢，還比他先貼出來。 :)

[大宇_]

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