SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query output for group by

USER101Mar 19 2009 — edited Nov 3 2010

Hi !

Can someone help ?
What I am looking for is, those days in the first query which do not return any rows should be displayed with a 0. I have all the days available in that table. Ie. 13/14/15th it should display as 0 instead of skipping it

SQL>  select trunc(logtimestamp),count(*)
from SOACOE_LOGGER_SUMMARY
where
   ERRORTYPE is not null
 group by trunc(logtimestamp)
 order by trunc(logtimestamp);  2    3    4    5    6

TRUNC(LOGTI   COUNT(*)
----------- ----------
05-MAR-2009          2
06-MAR-2009         13
07-MAR-2009          6
08-MAR-2009          1
09-MAR-2009          3
10-MAR-2009          3
12-MAR-2009          1
17-MAR-2009         36
18-MAR-2009         28

9 rows selected.

SQL> select distinct trunc(logtimestamp)
from SOACOE_LOGGER_SUMMARY
 order by trunc(logtimestamp);   2    3

TRUNC(LOGTI
-----------
05-MAR-2009
06-MAR-2009
07-MAR-2009
08-MAR-2009
09-MAR-2009
10-MAR-2009
11-MAR-2009
12-MAR-2009
13-MAR-2009
14-MAR-2009
15-MAR-2009

TRUNC(LOGTI
-----------
16-MAR-2009
17-MAR-2009
18-MAR-2009
19-MAR-2009

15 rows selected.

Thanks

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 17 2009

Added on Mar 19 2009

21 comments

1,494 views