How to get 0 value when count(*) returns null on multiply values

Question

I have a table that contains error codes and entry times (among other things).
What I need is a way to count the amount of lines with the same error code (that I choose) for the last hour, and to string the result the error code.

SELECT COUNT(*) || ',' || error_code as amount_and_code
FROM my_table
WHERE error_code in (5001, 5002, 5003, 5004, 5005)
AND entry_date >= (SYSDATE - 1/24)
group by error_code;

I get the obvious result of

AMOUNT_AND_CODE
---------------
4,5001
1,5002
2,5005

And my question is: How can I also return 0,error_code for values that were not found.
What I want to get is

AMOUNT_AND_CODE
---------------
4,5001
1,5002
0,5003
0,5004
2,5005

Is there a way getting the output I'm looking for?

Greatly appreciate your help, mod.

Edit: I do not have a table that contains all of the error codes.
Edit2: Oracle8i Enterprise Edition Release 8.1.7.4.0

Answer 1

You could try something like this :

SQL> create table nnn(error_code varchar2(4), entry_date date);

Table created.

SQL> insert into nnn values (5001, sysdate);

1 row created.

SQL> insert into nnn values (5003, sysdate - 10);

1 row created.

SQL>
SQL> with tbl as
  2  (select 5001 error_code from dual union all
  3   select 5002 error_code from dual union all
  4   select 5003 error_code from dual union all
  5   select 5004 error_code from dual)
  6  select count(nnn.error_code), tbl.error_code
  7  from   nnn, tbl
  8  where  nnn.error_code(+) = tbl.error_code
  9  and    entry_date(+) >= (SYSDATE - 1/24)
 10  group  by tbl.error_code;

COUNT(NNN.ERROR_CODE) ERROR_CODE
--------------------- ----------
                    0       5003
                    1       5001
                    0       5002
                    0       5004

SQL>

Answer 2

Do you have a table of error codes? If so then you can do this:

SELECT COUNT(my_table.id) || ',' || e.error_code as amount_and_code
FROM error_codes e
LEFT OUTER JOIN my_table ON my_table.error_code = e.error_code
                        AND my_table.entry_date >= (SYSDATE - 1/24)
WHERE e.error_code in (5001, 5002, 5003, 5004, 5005)
group by e.error_code;

If not then try:

WITH error_codes as
    ( SELECT 5001 FROM DUAL
      UNION ALL
      SELECT 5002 FROM DUAL
      UNION ALL
      SELECT 5003 FROM DUAL
      UNION ALL
      SELECT 5004 FROM DUAL
      UNION ALL
      SELECT 5005 FROM DUAL
    )
SELECT COUNT(my_table.id) || ',' || e.error_code as amount_and_code
FROM error_codes e
LEFT OUTER JOIN my_table ON my_table.error_code = e.error_code
                        AND my_table.entry_date >= (SYSDATE - 1/24)
group by e.error_code;