Is there a "better" way to refactor the query below that returns the number occurrences of a particular value (e.g. 'A') for each distinct id? The challenge seems to be keeping id = 2 in the result set even though the count is zero (id = 2 is never related to 'A'). It has a common table expression, NVL function, in-line view, distinct, and left join. Is all of that really needed to get this job done? (Oracle 19c)
create table T (id, val) as
select 1, 'A' from dual
union all select 1, 'B' from dual
union all select 1, 'A' from dual
union all select 2, 'B' from dual
union all select 2, 'B' from dual
union all select 3, 'A' from dual
;
with C as (select id, val, count(*) cnt from T where val = 'A' group by id, val)
select D.id, nvl(C.cnt, 0) cnt_with_zero from (select distinct id from T) D left join C on D.id = C.id
order by id
;
ID CNT_WITH_ZERO
---------- -------------
1 2
2 0
3 1
A simple way is conditional aggregation:
select id,
sum(case when val = 'A' then 1 else 0 end) as num_As
from t
group by id;
If you have another table with one row per id, you I would recommend:
select i.id,
(select count(*) from t where t.id = i.id and t.val = 'A') as num_As
from ids i;