I have a table CUST_LOG
with data below.
+----+---------+--------+ | ID | CUST_ID | STATUS | +----+---------+--------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 | 2 | | 4 | 3 | 3 | | 5 | 1 | 1 | | 6 | 1 | 2 | | 7 | 1 | 3 | | 8 | 2 | 1 | | 9 | 2 | 2 | +----+---------+--------+
How should I select above as below result? I want to group by CUST_ID
then sum each status count by CUST_ID
? I used OUTER JOIN
but not work.
+---------+----------+----------+----------+ | CUST_ID | STATUS_1 | STATUS_2 | STATUS_3 | +---------+----------+----------+----------+ | 1 | 2 | 2 | 1 | | 2 | 1 | 2 | 0 | | 3 | 0 | 0 | 1 | +---------+----------+----------+----------+
The only way I found is use UNION
clause as the following sql, but it is too complicated if there are many status.
select CUST_ID, SUM(STATUS_1) as STATUS_1, SUM(STATUS_2) as STATUS_2, SUM(STATUS_3) as STATUS_3 from ( select CUST_ID, COUNT(ID) as STATUS_1, 0 as STATUS_2, 0 as STATUS_3 from CUST_LOG where STATUS = 1 group by CUST_ID union select CUST_ID, 0 as STATUS_1, COUNT(ID) as STATUS_2, 0 as STATUS_3 from CUST_LOG where STATUS = 2 group by CUST_ID union select CUST_ID, 0 as STATUS_1, 0 as STATUS_2, COUNT(ID) as STATUS_3 from CUST_LOG where STATUS = 3 group by CUST_ID ) group by CUST_ID;
Advertisement
Answer
try the following, here is the demo.
select cust_id, sum(case when status = 1 then 1 else 0 end) status_1, sum(case when status = 2 then 1 else 0 end) status_2, sum(case when status = 3 then 1 else 0 end) status_3 from cust_log group by cust_id order by cust_id
Output:
| cust_id | status_1 | status_2 | status_3 | | ------- | -------- | -------- | -------- | | 1 | 1 | 1 | 0 | | 2 | 0 | 1 | 0 | | 3 | 0 | 0 | 1 |