CODEWITHSUNDEEP
sqloracle-database
Antonio Caserta
Antonio Caserta

Reputation: 13

Group and then count in Oracle

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.

Upvotes: 1

Views: 49

Answers (4)

LukStorms
LukStorms

Reputation: 29677

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;

Upvotes: 1

Mahamoutou
Mahamoutou

Reputation: 1555

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

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;

Upvotes: 0

Sergey
Sergey

Reputation: 5250

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

Upvotes: 0

Related Questions