CODEWITHSUNDEEP
sqloracle-database
matthung
matthung

Reputation: 45

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;

Upvotes: 0

Views: 95

Answers (2)

zealous
zealous

Reputation: 7503

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        |

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You are looking for a pivot query:

SELECT
    CUST_ID,
    CASE WHEN COUNT(CASE WHEN STATUS = 1 THEN 1 END) > 0 THEN 1 ELSE 0 END AS STATUS_1,
    CASE WHEN COUNT(CASE WHEN STATUS = 2 THEN 1 END) > 0 THEN 1 ELSE 0 END AS STATUS_2,
    CASE WHEN COUNT(CASE WHEN STATUS = 3 THEN 1 END) > 0 THEN 1 ELSE 0 END AS STATUS_3
FROM CUST_LOG
GROUP BY
    CUST_ID
ORDER BY
    CUST_ID;

Upvotes: 0

Related Questions