Count on case Oracle

Question

WE have below data in oracle database -

col1 col2 
Z1     A
Z1     B
Z2     A
Z2     C
Z3     A   
Z4     D

I want count on column two in such a way that -

Ouput -

col2  count
A      3     (Z1,Z2,Z3)
B      0     (Dont count if A is already present for record)
C      0
D      1      (Z4)

Best Regards

Answer 1

Thanks Guys. But I could do this way -

select  count(case
            when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) like '%A%' then 1
            else null
        end) A,
        count(case
            when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) = 'B' then 1
            else null
        end) B,
        count(case
            when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) = 'C' then 1
            else null
        end)  C,
         count(case
            when (LISTAGG(col2,'-') WITHIN GROUP (ORDER BY col2)) = 'D' then 1
            else null
        end) D
from T
GROUP BY col1

Thanks for your replies