这个SQL怎么写？？？

Question

select t.project_id,t.project_stage,count(*) group by t.project_id,t.project_stage这个sql可以查出分组合计值，如下：
项目A stageA 12
项目A stageB 3
项目A stageC 6
项目B stageB 8但我们想直接获得这样的结果：[因为stage是固定的。]项目A 12 3 6
项目B  0 8 0请问这样的SQL怎么写?

Answer 1

select project_id,count(decode(project_stage,'stageA',1)) as countA,
       count(decode(project_stage,'stageB',1)) as countB
       count(decode(project_stage,'stageC',1)) as countC
from   tablename
group by project_id

Answer 2

少了个逗号：select project_id,count(decode(project_stage,'stageA',1)) as countA,
       count(decode(project_stage,'stageB',1)) as countB,
       count(decode(project_stage,'stageC',1)) as countC
from   tablename
group by project_id

Answer 3

select
    project_id,
    stageA = sum(decode(project_id,'stageA',1,0)),
    stageB = sum(decode(project_id,'stageB',1,0)), 
    stageC = sum(decode(project_id,'stageC',1,0))
from
    tabname
group by
    project_id

Answer 4

不能用count()，得用sum()select
    project_id,
    sum(decode(project_id,'stageA',1,0)) as stageA,
    sum(decode(project_id,'stageB',1,0)) as stageB, 
    sum(decode(project_id,'stageC',1,0)) as stageC
from
    tabname
group by
    project_id

Answer 5

回楼上，count是可以的，我把decode最后一个参数默认值设为空，count计算的时候不会空值的，如果都为空count还是0

Answer 6

select
    project_id,
    sum(case when project_id='stageA' then 1 else 0 end)  stageA,
    sum(case when project_id='stageB' then 1 else 0 end)  stageB, 
    sum(case when project_id='stageC' then 1 else 0 end)  stageC
from  tabname
group by  project_id

Answer 7

count和sum确实都可以，谢谢大家了。给分！