Re: Add zero for null in count?

dan-x_at_yahoo.com wrote in
news:1158180729.436070.319190_at_m73g2000cwd.googlegroups.com:

> Hey. I have a SQL*Plus query where I'm counting unique values. How
> can I get it to return a zero if no data matches the query's criteria?
>
> Here's my query:
> SELECT distinct r.dstr_nr, COUNT(DISTINCT r.RGSN_ID) "DAILY TOTAL"
> FROM RGSN r
> WHERE r.DSTR_NR between 1603 and 1700
> and trunc(applied_dt) = '12-SEP-2006'
> group by r.dstr_nr
> order by r.dstr_nr desc
> /
>
> Let's say it's displaying this right now...
> DSTR_NR DAILY TOTAL
> 1603 153
> 1604 23
> 1606 98
>
> I want it to display this...
> DSTR_NR DAILY TOTAL
> 1603 153
> 1604 23
> 1605 0
> 1606 98
> 1607 0
>
> Any help is appreciated.
> Thanks,
> -Danny
>

From where is Oracle supposed to "obtain" 1607? If 1607 does not exist in table RGSN, do you expect Oracle to automagically return 1607 just 'cuz that is part of your made up answer set. Received on Wed Sep 13 2006 - 21:08:48 CDT