theideabulb
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
select to_char(trunc(q.quote_dt,'
from q_quotes q
where q.quote_status_id in (8,10,13)
and to_char(q.quote_dt,'yyyy')
and q.quote_id not in (select linked_quote_id from q_quote_links)
group by to_char(trunc(q.quote_dt,'
order by to_char(trunc(q.quote_dt,'
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
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
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')
and q.quote_id not in (select linked_quote_id from q_quote_links)
group by to_char(trunc(q.quote_dt,'
order by to_char(trunc(q.quote_dt,'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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.
( 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
but it constructs the 12 months of 2009.
If you change 2009 to another year it will construct all 12 months for that year
ASKER
Awesome, great work!
ASKER
by the way, what does this mean:
TO_CHAR(LEVEL, 'fm09'),
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
TO_CHAR(1, '09') would return ' 01'
the fm removes the leading space
ASKER
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?