select colx,
sum(case when col=bm1 then 1 else 0 end) as bm1
sum(case when col=bm2 then 1 else 0 end) as bm2
from table
group by colX
sum(case when col=bm1 then 1 else 0 end) as bm1
sum(case when col=bm2 then 1 else 0 end) as bm2
from table
group by colX
sum(case when col=bm1 then 1 else 0 end) as bm1
sum(case when col=bm2 then 1 else 0 end) as bm2
from table
group by colX,col
see again, different recorderset different result column nums
Just Like Test Our E Level!!UP!
SUM(CASE WHEN bm='bm2' THEN bb ELSE NULL) as bm2
FROM(SELECT sp,bm,SUM(s1) as bb
from tab
group by sp,bm
) tmp
GROUP BY sp--想不出有其它情況(不止bm1,bm2)的方法。
this problem from
http://expert.csdn.net/Expert/topic/1229/1229844.xml?temp=9.800357E-02
(case when (select count(*) from table where col='bm1')>0 then (sum(case when (select count(*) from table where col='bm2')>0 then ((case when col=bm1 then 1 else 0 end) as bm1) end))
sum(case when col=bm2 then 1 else 0 end) as bm2
from table
group by colX
(case
when (select count(*) from table where col='bm1')>0 then
(sum(case when col=bm1 then 1 else 0 end) as bm1)
end)(case
when (select count(*) from table where col='bm2')>0 then
(sum(case when col=bm2 then 1 else 0 end) as bm2)
end)
from table
group by colX