Home » SQL & PL/SQL » SQL & PL/SQL » Displaying Count as 0 for no records (Oracle 9)
Displaying Count as 0 for no records [message #314404] Wed, 16 April 2008 10:50 Go to next message
sanjeevsh
Messages: 4
Registered: April 2008
Junior Member
Period Sector Activity likelihood COUNT

9/1/2007 CC AB High 2


9/1/2007 CC TEST High 0



If you look at above, you can see the count of 2 for likelihood high but for the other activity i dont have a record in the database but i have to display the count as zero.I cannot hard code the names.Please let me know if there is any sql query to pull a record from database if for one activity there is some count but If there is no record in d/b for the other.

Please help Thanks in advance
Re: Displaying Count as 0 for no records [message #314405 is a reply to message #314404] Wed, 16 April 2008 10:53 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
If there is NO record, then you are out of luck.
How do you know which names/labels are not in the database?
Re: Displaying Count as 0 for no records [message #314406 is a reply to message #314405] Wed, 16 April 2008 10:56 Go to previous messageGo to next message
sanjeevsh
Messages: 4
Registered: April 2008
Junior Member
If you look at the display there are column names,
but for one category there is a record where as if the record is not present for other activity i have to display count as 0

Re: Displaying Count as 0 for no records [message #314411 is a reply to message #314406] Wed, 16 April 2008 11:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Count already returns 0 when there are no rows.

SQL> SELECT Count(*) FROM dual WHERE 1=1;

  COUNT(*)
----------
         1

SQL>
SQL>
SQL> SELECT Count(*) FROM dual WHERE 1=2;

  COUNT(*)
----------
         0


So far you have not posted enough information. We don't know what your data is, we only have some vague information what the output should be.

Post at least formatted example data, better yet create table and insert statements for a test case, and post what you have already tried.

Re: Displaying Count as 0 for no records [message #314412 is a reply to message #314411] Wed, 16 April 2008 11:23 Go to previous messageGo to next message
sanjeevsh
Messages: 4
Registered: April 2008
Junior Member
CREATE TABLE TEST_TABLE(PERIOD DATE,SECTOR VARCHAR2(200),LIKELIHOOD VARCHAR2(30),COUNT NUMBER(10))


INSERT INTO TEST_TABLE VALUES ('9/1/2007','cc','EOL',1);
INSERT INTO TEST_TABLE VALUES ('9/1/2007','cc','EOL',1);
INSERT INTO TEST_TABLE VALUES ('9/1/2007','cc','EOL',1);
INSERT INTO TEST_TABLE VALUES ('9/1/2007','cc','EOL',1);
INSERT INTO TEST_TABLE VALUES ('9/1/2007','cc','EOL',1);
INSERT INTO TEST_TABLE VALUES ('9/1/2007','cc','EOL',1);
INSERT INTO TEST_TABLE VALUES ('9/1/2007','cc','EOL',1);
INSERT INTO TEST_TABLE VALUES ('9/1/2007','cc','EOL',1);


SELECT PERIOD, SECTOR, LIKELIHOOD, COUNT(*)
FROM TEST_TABLE WHERE UPPER(SECTOR )='CC' GROUP BY PERIOD, SECTOR,LIKELIHOOD

now this query would display a result of this kind

PERIOD SECTOR LIKELIHOOD COUNT
9/1/2007 CC EOL 7

But my requirement is there will be different sectors for those sectors since there are no records i have to combine the result and display the result as


PERIOD SECTOR LIKELIHOOD COUNT
9/1/2007 CC EOL 7
9/1/2007 TEST EOL 0


I hope i'm bit clear with this details

Re: Displaying Count as 0 for no records [message #314419 is a reply to message #314412] Wed, 16 April 2008 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How could Oracle know you want a line for TEST and not for GBJDHGHCVXUYG?

Regards
Michel
Re: Displaying Count as 0 for no records [message #314442 is a reply to message #314419] Wed, 16 April 2008 12:47 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Michel Cadot wrote on Wed, 16 April 2008 12:42
How could Oracle know you want a line for TEST and not for GBJDHGHCVXUYG?



I think you meant GBJDHGHCVXUGG.
Re: Displaying Count as 0 for no records [message #314443 is a reply to message #314442] Wed, 16 April 2008 12:58 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You say "there will be different sectors".

If these different sectors/likelihoods are be available in some table, then you can join to that table into the query.

Also : what if there is a day for which NO entries are in the table? If in that case all sectors and likelihoods should be displayed with 0, then you also need some source for all possible dates.

Basically you will probably need one table or view with all possible dates/sectors/likelihoods, and join the data table to that in a left outer join.
Re: Displaying Count as 0 for no records [message #314444 is a reply to message #314443] Wed, 16 April 2008 13:01 Go to previous messageGo to next message
sanjeevsh
Messages: 4
Registered: April 2008
Junior Member
I have to create a view to display those values.
and there is only one table.That is my concern
Re: Displaying Count as 0 for no records [message #314447 is a reply to message #314444] Wed, 16 April 2008 13:45 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When there is only that table, and the other things that have to be displayed are not in the database then it can not be done.

You will have to create the other tables if they are not there.

This should get you started :

create table sectors (
  sector varchar2(10)
);

insert into sectors values ('aa');
insert into sectors values ('bb');
insert into sectors values ('cc');
insert into sectors values ('dd');

create table likelihoods (
  likelihood varchar2(20)
);

insert into likelihoods values ('EOL');
insert into likelihoods values ('TEST');
insert into likelihoods values ('GBJDHGHCVXUYG');

select sector  , likelihood
  from sectors , likelihoods;
 
Previous Topic: commit in side stored procedure
Next Topic: ORA-27041: unable to open file SVR4 Error: 24: Too many open files ...
Goto Forum:
  


Current Time: Fri Dec 09 03:40:23 CST 2016

Total time taken to generate the page: 0.10555 seconds