Oracle sql to count instances of different values in single column - continuation

Question

This is in continuation to an earlier question I had posted Here is the link Oracle sql to count instances of different values in single column

In further continuation to the pivot query, I am trying to do something like

for col in ( Count_status20 as col20, Count_status30 or Count_status40 as col30, Count_status50 as col50) The input remaning the same as earlier question.

Basically here I am trying to sum statuses in 30 or 40 as one column.

Answer 1

Try it like this:

select *
from
(
  select tkey, status, 
    decode(status, 30, 30, 40, 30,status) as col
  from tableB b
  left join tableA a
    on a.fkey = b.fkey
) src
pivot
(
  count(status)
  for col in ('20' as Count_Status20, 
              '30' as Count_Status3040,
              '50' as Count_Status50)
) piv;

Here is a fiddle