We help IT Professionals succeed at work.

sql query with count returns null rather than zero

dpozega
dpozega asked
on
Medium Priority
946 Views
Last Modified: 2011-10-03
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'
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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'
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
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'
CERTIFIED EXPERT
Top Expert 2011
Commented:
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'

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

Author

Commented:
I typed in the original formula wrong, using t1 for minutes.

Author

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'

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.