Group and Case question 1

[mimi2]

Hello.
what is missing in my statement?
i need to count the number of Ns and Ys in my table.

select
CASE WHEN (P.ID =1 OR P.ID=4 OR P.ID=22 OR P.ID=2)
THEN 'N' ELSE 'Y' END AS SUB, count(SUB)
from MAIN P
group by SUB

 

[nigelrivett]

something like

select
cnt_N = sum(CASE WHEN P.ID =1 OR P.ID=4 OR P.ID=22 OR P.ID=2 then 1 else 0 end) ,
cnt_Y = sum(CASE WHEN P.ID =1 OR P.ID=4 OR P.ID=22 OR P.ID=2 then 0 else 1 end)
from MAIN P


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mimi2]

thanks