Home » SQL & PL/SQL » SQL & PL/SQL » How to find out no rows in a SQL
How to find out no rows in a SQL [message #193808] Tue, 19 September 2006 09:06 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
I have a table containing a list of computername, time of error and error message. I want to find out how many errors (just a count would do) were logged for each computername and if there are no rows for a particular computer, it should display 0 rows against the computername. I provide the computername as part of the in list. For eg.

select count(*), computername
from comp_error
group by computername
having computername in ('abc','testdc01','sgsss33','sdsfbox');


Is it possible to do this with a sql statement ?
Re: How to find out no rows in a SQL [message #193818 is a reply to message #193808] Tue, 19 September 2006 09:36 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
select computername, count(error_message)
from comp_error
where computername in ('abc','testdc01','sgsss33','sdsfbox')
group by computername;
Re: How to find out no rows in a SQL [message #193819 is a reply to message #193818] Tue, 19 September 2006 09:48 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
In this case however if there are no rows for computer 'sdsfbox' like no error messages were ever logged, then that computername will not show in the query output. I want to show 'sdsfbox' 0 as the count in case there are no rows.
Re: How to find out no rows in a SQL [message #193827 is a reply to message #193819] Tue, 19 September 2006 10:25 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Requirements...they getcha everytime...

select nvl(computername, b.value) "Computername", count(error_message)
from comp_error a,
	(select column_value as value from
	 table(sys.dbms_debug_vc2coll('abc','testdc01','sgsss33','sdsfbox'))) b
where b.value = a.computername(+)
group by nvl(computername, b.value);

[Updated on: Tue, 19 September 2006 10:40]

Report message to a moderator

Re: How to find out no rows in a SQL [message #193836 is a reply to message #193827] Tue, 19 September 2006 10:47 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Thanks for the query. But I get the count(*) as 1 instead of 0 for a non existant computer.
Re: How to find out no rows in a SQL [message #193837 is a reply to message #193836] Tue, 19 September 2006 11:02 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Don't know why...

SQL> select * from comp_error;

COMPUTERNAME		ERROR_MESSAGE
---------------------- ----------------
abc			Error 1
abc			Error 2
abc			Error 3
testdc01		Error 1

SQL> select nvl(computername, b.value) "Computername", count(error_message)
  2  from comp_error a,
  3	(select column_value as value from
  4	 table(sys.dbms_debug_vc2coll('abc','testdc01','sgsss33','sdsfbox'))) b
  5  where b.value = a.computername(+)
  6  group by nvl(computername, b.value);

COMPUTERNAME		COUNT(ERROR_MESSAGE)
---------------------- ----------------------
abc					   3
testdc01				   1
sgsss33					   0
sdsfbox					   0


What version are you using?
Re: How to find out no rows in a SQL [message #193840 is a reply to message #193837] Tue, 19 September 2006 11:38 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
It seems to work now. I made a mistake maybe. Thanks for the query.
Re: How to find out no rows in a SQL [message #193876 is a reply to message #193808] Tue, 19 September 2006 15:36 Go to previous message
shoblock
Messages: 325
Registered: April 2004
Senior Member
the reason you got "1" instead of "0" was the because of "count(*)" instead of "count(error_message)"

on the rows where count(error_message)=0, count(*) will return 1, because there is 1 row - the row displaying the computer name. but count(error_message) will return 0 because on the outer-joined rows, its possible that no error_messages exist.
Previous Topic: Oracle 8i installation problems.
Next Topic: horizontal data by column name
Goto Forum:
  


Current Time: Thu Dec 08 22:15:05 CST 2016

Total time taken to generate the page: 0.15635 seconds