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

Question

I have a table containing error codes and login time (by the way).
I need a way to count the number of lines with the same error code (which I choose) during the last hour and the result line of 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

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

And my question is: how can I return 0,error_code for values ​​that were not found.
I want to receive

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

Is there any way to get the conclusion I'm looking for?

Thank you very much for your help, mod.

Edit: I do not have a table containing all error codes.
Edit: Oracle8i Enterprise Edition Release 8.1.7.4.0

Answer 1

Will the error codes always be in order? You should use the connection in this 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 just want to show all the 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 ; 

It really just gives you an account of all the error codes in the table, and then choosing a time based score

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

note that this can be resource intensive

if you need only certain numbers (these are 5001,5002,5003, ####, #### 2, ..., ### x), then you can try something like this (this requires a type and function 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 ; 

Answer 2

You can 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 3

Do you have a table of error codes? If so, 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, 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; 

Answer 4

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; 

Answer 5

I hope entry_date >= (SYSDATE - 1/24) filters the records you need (i.e. 5003 and 5004).

After removing this condition and checking the result.

or

Try a query that might 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; 

Answer 6

Make a query as if you had an external table, without "WITH", not available 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; 

Answer 7

Do you have a main table listing your error codes? If so, I would use an outer join for this table, so you are guaranteed to return a row for each record.