sql query with count returns null rather than zero

Hi All,

I'm using a sql query (on SQL server 2000) to return counts on a database table (table1) and a database view(table2).  The query uses count(), and it returns NULL when the count is zero.  Is there a workaround for this?  I am displaying this data in a table and want the zero counts to show up with the value of "0".  I have a sample of my code below.

select distinct t1.tech, t1.field2, t1.field3,
          (select count(t2.orderid) from dbo.table2 t2
           where t1.minutes<1440 and t2.tech=t1.tech group by t2.tech) as "Less than 1 day",
          (select count(t2.orderid) from dbo.table2 t2
           where t1.minutes>=1440 and t2.tech=t1.tech group by t2.tech) as "More than 1 day",
          (select count(t2.orderid) from dbo.table2 t2
           where t2.tech=t1.tech group by t2.tech) as "Total"
from dbo.table1 t1
where t1.orderstatus<>'closed'
dpozegaAsked:
Who is Participating?
 
LowfatspreadCommented:
isn't this what
your running?

select distinct t1.tech, t1.field2, t1.field3,
          coalesce(case when minutes <1440 then ords else 0 end,0) as "Less than 1 day",
          coalesce(case when minutes>=1440 then ords else 0 end,0) as "More than 1 day",
          coalesce(ord,0) as "Total",
from dbo.table1 t1
inner join
   (select tech,count(orderid) as ords
      from table2
     group by tech
   ) as t2
    on t1.tech=t2.tech
where t1.orderstatus<>'closed'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
select distinct t1.tech, t1.field2, t1.field3,
          (select ISNULL(count(t2.orderid),0) from dbo.table2 t2
           where t1.minutes<1440 and t2.tech=t1.tech group by t2.tech) as "Less than 1 day",
          (select ISNULL(count(t2.orderid),0) from dbo.table2 t2
           where t1.minutes>=1440 and t2.tech=t1.tech group by t2.tech) as "More than 1 day",
          (select ISNULL(count(t2.orderid),0) from dbo.table2 t2
           where t2.tech=t1.tech group by t2.tech) as "Total"
from dbo.table1 t1
where t1.orderstatus<>'closed'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Also try this


select distinct t1.tech, t1.field2, t1.field3,
          (select sum(1) from dbo.table2 t2
           where t1.minutes<1440 and t2.tech=t1.tech group by t2.tech) as "Less than 1 day",
          (select sum(1) from dbo.table2 t2
           where t1.minutes>=1440 and t2.tech=t1.tech group by t2.tech) as "More than 1 day",
          (select sum(1) from dbo.table2 t2
           where t2.tech=t1.tech group by t2.tech) as "Total"
from dbo.table1 t1
where t1.orderstatus<>'closed'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dpozegaAuthor Commented:
aneeshattingal,

Thanks, but I'm still returning null instead of zero.  I tried both methods.  Neither one throws an error, but neither returns the zeros.

Lowfatspread,

Almost works, but the minutes field is only in table2 and I get an error that the column doesn't exist, even when I try to qualify it.

Thanks,
Dee
0
 
dpozegaAuthor Commented:
I typed in the original formula wrong, using t1 for minutes.
0
 
dpozegaAuthor Commented:
Here is the code that returns the nulls,

select distinct t1.tech, t1.field2, t1.field3,
        (select count(t2.orderid) from dbo.table2 t2
         where t2.minutes<1440 and t2.tech=t1.tech group by t2.tech) as "Less than 1 day",
        (select count(t2.orderid) from dbo.table2 t2
         where t2.minutes>=1440 and t2.tech=t1.tech group by t2.tech) as "More than 1 day",
        (select count(t2.orderid) from dbo.table2 t2
         where t2.tech=t1.tech group by t2.tech) as "Total"
from dbo.table1 t1
where t1.orderstatus<>'closed'
0
 
dpozegaAuthor Commented:
Great, I switched the order around and it works.  Thanks a bunch.  Here's the code I ended up using:

select distinct t2.tech,
          coalesce(case when minutes <1440 then ords else 0 end,0) as "Less than 1 day",
          coalesce(case when minutes>=1440 then ords else 0 end,0) as "More than 1 day",
          coalesce(ords,0) as "Total"
from dbo.table2 t2
inner join
   (select t.tracker, count(t.orderid) as ords
      from dbo.table1 t
      where t.orderstatus<>'closed'
      group by tech) t1
    on t1.tech=t2.tech
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.