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

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

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>

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;

Are the error codes always going to be in order? You ought be able to utilize a connect by in that case as such:

   SELECT COUNT(MY_TABLE.ERROR_CODE) || ',' || ERROR_CODES.error_code as amount_and_code
FROM 
    (
        SELECT (5000+LEVEL) error_code 
        FROM DUAL 
        CONNECT BY LEVEL <= 5    
    ) ERROR_CODES
    LEFT JOIN
    (
        SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        UNION ALL
        SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        union all
        SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        union all
        SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        union all
        SELECT 5002 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        union all
        SELECT 5005 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        UNION all
        SELECT 5005 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
    ) MY_TABLE
      ON MY_TABLE.ERROR_CODE        = ERROR_CODES.ERROR_CODE
           AND MY_TABLE.ENTRY_DATE >= (SYSDATE - 1/24)
GROUP BY       ERROR_CODES.ERROR_CODE
order by  ERROR_CODES.error_code
;

if you simply want to show all errors you can try this

with MY_TABLE as(
        SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        UNION ALL
        SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        union all
        SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        union all
        SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        union all
        SELECT 5002 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        union all
        SELECT 5005 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        UNION ALL
        SELECT 5005 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
        UNION ALL
        SELECT 5003 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL
        UNION ALL
        SELECT 5004 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL
        UNION ALL
        SELECT 5006 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL
        UNION ALL
        SELECT 5010 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL
        UNION ALL
        SELECT 6018 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL        
) 
 SELECT COUNT(MY_TABLE.ERROR_CODE) || ',' || ERROR_CODES.error_code as amount_and_code
FROM 
    (
        SELECT DISTINCT ERROR_CODE
        FROM MY_TABLE --**warning** this could be resource intensive    
    ) ERROR_CODES
    LEFT JOIN
     MY_TABLE
      ON MY_TABLE.ERROR_CODE        = ERROR_CODES.ERROR_CODE
           AND MY_TABLE.ENTRY_DATE >= (SYSDATE - 1/24)
GROUP BY       ERROR_CODES.ERROR_CODE
order by  ERROR_CODES.error_code
;

This is really just giving you a distinct on all the error codes in the table then selecting the count based off of time

        (
        SELECT DISTINCT ERROR_CODE
        FROM MY_TABLE --**warning** this could be resource intensive    
    ) ERROR_CODES

please note, this can be resource intensive

if you only want specific numbers (that is 5001,5002,5003,####,####2,...,###x) then you can try something like this (this requires a custom type and a function to be created):

--see  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425
--create a table of numbers ,create a function to split the "where" into a table
--then return the data
CREATE OR REPLACE type numberTableType as table      of number;
/
create or replace function in_number(  p_string in varchar2 ) return numberTableType  AS
        l_string        long default p_string || ',';
        l_data          numberTableType := numberTableType();
        l_number        number ;
        N               NUMBER;
    BEGIN

      loop
          exit when l_string is null;
          n := instr( l_string, ',' );
         l_data.extend;
         begin --is user inputs a non-numeric value  skip the value
            l_number := cast(ltrim( rtrim( substr( l_string, 1, n-1 ) ) )  as number);
            l_data(l_data.count) := l_number ;
            EXCEPTION
                    WHEN VALUE_ERROR THEN
                        l_number := 0;
                    WHEN OTHERS THEN
                        raise ;
         end ;
         l_string := substr( l_string, n+1 );
    end loop;
    RETURN L_DATA;
  END in_number;
  /

 SELECT COUNT(MY_TABLE.ERROR_CODE) || ',' || ERROR_CODES.error_code as amount_and_code
FROM 
    (
        SELECT COLUMN_VALUE AS ERROR_CODE 
          from table(in_number('5001,5002,5003,5004,5005,5010'))    
    ) ERROR_CODES
    LEFT JOIN
        (
            SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
            UNION ALL
            SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
            union all
            SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
            union all
            SELECT 5001 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
            union all
            SELECT 5002 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
            union all
            SELECT 5005 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
            UNION ALL
            SELECT 5005 ERROR_CODE, (SYSDATE - 1/26) ENTRY_DATE FROM DUAL
            UNION ALL
            SELECT 5003 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL
            UNION ALL
            SELECT 5004 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL
            UNION ALL
            SELECT 5006 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL
            UNION ALL
            SELECT 5010 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL
            UNION ALL
            SELECT 6018 ERROR_CODE, (SYSDATE - 1/23) ENTRY_DATE FROM DUAL            
        )
     MY_TABLE
      ON MY_TABLE.ERROR_CODE        = ERROR_CODES.ERROR_CODE
           AND MY_TABLE.ENTRY_DATE >= (SYSDATE - 1/24)
GROUP BY       ERROR_CODES.ERROR_CODE
ORDER BY  ERROR_CODES.ERROR_CODE
;  

Have you tried...

SELECT NVL (COUNT(*), 0) || ',' || 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 hope that entry_date >= (SYSDATE - 1/24) is filtering the records which were needed by you (i.e., 5003 & 5004).

Once remove that condition and check the result.

or

Try below query it may solve your problem

SELECT COUNT(error_code) || ',' || 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; 

Make a request like if you was having a extern table, without the 'WITH' not avaible in Oracle 8... Very similar to Tony's code :

SELECT COUNT(t1.error_code) || ',' || t2.error_code as amount_and_code
FROM my_table t right outer join 
                    (
                        select 5001 as error_code from dual
                        union
                        select 5002 as error_code from dual
                        union
                        select 5003 as error_code from dual
                        union
                        select 5004 as error_code from dual
                        union
                        select 5005 as error_code from dual
                    ) t2 on t1.error_code = t2.error_code
                         and t.entry_date >= (SYSDATE - 1/24)
group by t2.error_code;

Do you have a master table that lists your error codes? If so, I would outer join to that table, so that you are guaranteed to return a row for each entry.