Display row where count(*) is zero?

Question:  I need to write a SQL that counts all rows within a single table using an "in" list, and including all rows counts, even where the row count is zero.  Here is my query:

select
   sts_id,
   count(*) mycount
from
   task
where
   sts_id in (1, 3, 4, 5, 6, 7, 8, 30, 39, 40, 41, 42)
group by sts_id;

sts_id     mycount
-------    -----------
1          48
8           1
39         16
42         76

As you see, I cannot display the rows where the count(*) is zero, and the in list seems to prevent the display of matching rows where the count(*) is zero.  How do I get the rows with zero values to appear in my SQL output?

Answer:  If you were doing this count with a table join, it’s easy to invoke an “outer join” to include missing rows, but here you have only a single table. 

Because the "IN" list restricts which rows are selected, values with no row will be displayed unless we create them in a temporary table:

create table in_list (sts_id number);
insert into in_list values (1);
insert into in_list values (3);
 . . . .
insert into in_list values (42);

Now, you can simple use this temporary table in an outer join to include the missing rows, something like this:

select
   in_list.sts_id,
   count(*) mycount
from
   task
left outer join
   in_list
group by sts_id;

Oracle guru Steve Callan offers this PL/SQL solution to displaying rows where count is zero.  He notes that you would need to create a temporary create a table with just those values (and a column for the count) and modify the code to select sts_id from the new table, and update the new table with the count:

If the sts_id values do not exist in task, then create a table with just those values (and a column for the count) and modify the code to select sts_id from the new table, and update the new table with the count. Count will always return a number.

If you have duplicate sts_id values, then you need to account for them as well:

drop table task;
create table task (id number, line number);
insert into task values (1,1);
insert into task values (1,2);
insert into task values (2,null);
commit;
set serveroutput on
declare
  v_cnt integer;
  cursor c is
  select distinct id from task;  
begin
  for r in c loop
    select count(*) into v_cnt
    from task
    where id = r.id
    and line > 0;
    dbms_output.put_line(r.id||' '||v_cnt);
  end loop;
end;
/