Group and then count in Oracle-CodePudding

I'm trying to do this in Oracle with count() and subqueries but i can't achieve what I want and I don't see any question with the same issue.

So I have this table:

ID          STATUS
---------   ------
1           A
1           A
1           B
2           A
2           B   
2           C
3           A
3           C
3           C

And I want something like this:

ID          A       B       C
---------   -       -       -
1           2       1       0
2           1       1       1
3           1       0       2

So grouping by ID and showing how many "status" of each kind are by "ID". Maybe I need a procedure? Can I do this with a simple query?

Thanks in advance.

CodePudding user response：

You could try to pivot them.

SELECT * 
FROM (
    SELECT ID, STATUS 
    FROM yourtable
) src
PIVOT (
    COUNT(*) 
    FOR STATUS IN ('A' A, 'B' B, 'C' C)
) pvt;

CodePudding user response：

SELECT C.ID,
SUM(
    CASE
      WHEN C.STATUS='A' THEN 1
    ELSE 0
  END) COUNT_A,
SUM(
    CASE
     WHEN C.STATUS='B' THEN 1
    ELSE 0
END) COUNT_B,
SUM(
   CASE
     WHEN C.STATUS='C' THEN 1
     ELSE 0
  END) COUNT_C
FROM YOUR_TABLE C
GROUP BY C.ID;

It is called "conditional aggregation". Please take a look on the above query if it is suitable for you

CodePudding user response：

You want conditional aggregation. I prefer this abbreviated version:

SELECT ID, COUNT(CASE WHEN STATUS = 'A' THEN 1 END) AS A,
           COUNT(CASE WHEN STATUS = 'B' THEN 1 END) AS B,
           COUNT(CASE WHEN STATUS = 'C' THEN 1 END) AS C
FROM yourTable
GROUP BY ID;

CodePudding user response：

The PIVOT clause is perfect for this.

select *
from t
pivot (
count(status) for status in (
     'A' as A
    ,'B' as B
    ,'C' as C
  )
)
;

demo