[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 931
  • Last Modified:

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'
0
dpozega
Asked:
dpozega
  • 4
  • 2
1 Solution
 
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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now