dpozega
asked on
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'
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'
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'
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'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
I typed in the original formula wrong, using t1 for minutes.
ASKER
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'
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'
ASKER
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
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
(select ISNULL(count(t2.orderid),0
where t1.minutes<1440 and t2.tech=t1.tech group by t2.tech) as "Less than 1 day",
(select ISNULL(count(t2.orderid),0
where t1.minutes>=1440 and t2.tech=t1.tech group by t2.tech) as "More than 1 day",
(select ISNULL(count(t2.orderid),0
where t2.tech=t1.tech group by t2.tech) as "Total"
from dbo.table1 t1
where t1.orderstatus<>'closed'