如何在sql中获取基于状态id的数据计数

Question

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

但是我想要像一样的结果

id    data1 data2+data3
100    1     2
101    1     1

我正在使用以下查询：

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

任何帮助。

谢谢。苏达。

Answer 1

您可以将状态2和3一起计数，如下所示：

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

Answer 2

您可以使用PIVOT子句来获得所需的结果。

使用DECODE映射状态，使2和3产生相同的值

with tab2 as (select 
id, decode(status_id,3,2,status_id) status_id2
from tab)
select * from tab2
PIVOT (count(*) "CNT"  for status_id2 in 
(1 as "DATA_1",
2 as "DATA_2_3")
)
ID DATA_1_CNT DATA_2_3_CNT
---------- ---------- ------------
100          1            2
101          1            1