Skip to content
Advertisement

Oracle SQL group by then sum

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        |
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement