how to get the count of data based on status id in sql

 i have following table
 
    id  statusid
    100   1
    100   2
    100   3
    101   1
    101   3 

  i am getting the result like following  

id    data1 data2 data3
100    1     1     1
101    1     0     1

but i want the result like following

id    data1 data2+data3
100    1     2
101    1     1

i am using the following query:

select id, SUM(CASE WHEN statusid=1 THEN 1 ELSE 0 END) AS data1,
SUM(CASE WHEN statusid=2 THEN 1 ELSE 0 END) AS data2,
SUM(CASE WHEN statusid=3 THEN 1 ELSE 0 END) AS data3
from employee
group by id

any help.

thank you. Sudha.

Answer

You can count statuses 2 and 3 together like so:

select id, 
    sum(case when statusid = 1 then 1 else 0 end) as data1,
    sum(case when statusid in (2, 3) then 1 else 0 end) as data2_3
from employee
group by id

Advertisement

Answer