Link to home
Start Free TrialLog in
Avatar of theideabulb
theideabulbFlag for United States of America

asked on

Help with returning value of zero

I have a grouped query here that counts.   I need to know if there is a way to return a zero if there is no count so that the month will show up and show zero.

select to_char(trunc(q.quote_dt,'MM'), 'mm yyyy') AS theMonth, count(q.quote_id) AS NumOfLosses
from q_quotes q
where q.quote_status_id in (8,10,13)
and to_char(q.quote_dt,'yyyy') = '2009'
and q.quote_id not in (select linked_quote_id from q_quote_links)
group by to_char(trunc(q.quote_dt,'MM'), 'mm yyyy')
order by to_char(trunc(q.quote_dt,'MM'), 'mm yyyy') ASC

As you can see in the output below, there was no entry for January 2009:

 THEMONTH     NUMOFLOSSES    
 -----------  --------------
 02 2009      115            
 03 2009      255            
 04 2009      289            
 05 2009      234            
 06 2009      255            
 07 2009      308            
 08 2009      372            
 09 2009      339            
 10 2009      310            
 11 2009      242            
 12 2009      221

Avatar of Sean Stuber
Sean Stuber

you will need to do an outer join between your q_quotes table and a table/view of months you want to return
select to_char(trunc(q.quote_dt,'MM'), 'mm yyyy') AS theMonth,
nvl(count(q.quote_id),0) AS NumOfLosses
from q_quotes q
where q.quote_status_id in (8,10,13)
and to_char(q.quote_dt,'yyyy') = '2009'
and q.quote_id not in (select linked_quote_id from q_quote_links)
group by to_char(trunc(q.quote_dt,'MM'), 'mm yyyy')
order by to_char(trunc(q.quote_dt,'MM'), 'mm yyyy') ASC
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
NVL isn't necessary if you do the outer join because the count will return 0.

If you don't do the outer join, NVL doesn't help because you won't have a NULL row to convert to 0
Thats correct
Avatar of theideabulb

ASKER

sdstuber, your solution is working, the others are not.. Can you explain what this is going.

(    SELECT TO_DATE('2009' || TO_CHAR(LEVEL, 'fm09'), 'yyyymm') m
                    FROM DUAL
              CONNECT BY LEVEL <= 12)


This will have to be dynamic to work with other years, so I want to make sure this isn't just for this one query.  
you can run that part to see it work

but it constructs the 12 months of 2009.

If you change 2009 to another year it will construct all 12 months for that year
Awesome, great work!
by the way, what does this mean:

TO_CHAR(LEVEL, 'fm09'),
converts level, which will be an integer 1-12 into a 2 character string

TO_CHAR(1, '09') would return ' 01'

the fm removes the leading space
Just wondering if this method can be used to do each day, so that I can I show counts for each day of the current month?