Re: Add zero for null in count?

dan-x_at_yahoo.com wrote:
> 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
> /

1. the DISTINCT in the "SELECT distinct r.dstr_nr," clause is totally unnecessary. The GROUP BY ensures there is only one r.dstr_nr row in the reslut set.
2. It would help if you give a sample of the data in the table. (IOW the input to this query)

>
> 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

3. Are you sure there are rows with DSTR_NR = 1605 or 1607 for that date in your table???
or are you sure you gave the actual query you are having a problem with???

You won't get any rows that don't meet the WHERE clause. As a test, since you are looking for only one date, put the date column in the result (ie, add it to the SELECT and GROUP BY clauses). Let us know what you find out.

 Ed Received on Wed Sep 13 2006 - 21:54:51 CDT