SQL Server Performance Forum – Threads Archive

Count() not returning 0-counts

Hey guys, I have an annoying problem on SQL Server 2005. When I do the following statement: select v.MeasureId, count(v.value) as countValues
from MinuteValues as v
where (v.Date between @beginDate and @endDate) and v.valid = 1
group by v.MeasureId it returns no rows. The thing is, there is no data in the DB matching the criteria in the WHERE clause, so what I’d like to receive is measureId countValues
1000 0
2000 0
3000 0
… but instead it just returns nothing, so basically all 0-counts are ommitted, cause when I leave the "and v.valid = 1" out of the where clause, then there is data matching the where clause and the counts are returned correctly. Anyone any idea ? THX

---

Something like this perhaps? select X.MeasureId, count(v.value) as countValues
from (SELECT MeasureId FROM MinuteValues GROUP BY MeasureId) as X
LEFT JOIN MinuteValues as v
ON X.MeasureId = v.MeasureId
where (v.Date between @beginDate and @endDate) and v.valid = 1
group by v.MeasureId

---

I guess it should be select X.MeasureId, count(v.value) as countValues
from (SELECT MeasureId FROM MinuteValues GROUP BY MeasureId) as X
LEFT JOIN MinuteValues as v
ON X.MeasureId = v.MeasureId
AND (v.Date between @beginDate and @endDate) and v.valid = 1
group by v.MeasureId
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com

---

Or perhaps like this ?
select v.MeasureId, sum(case when v.valid = 1 then 1 else 0 end) as countValues
from MinuteValues as v
where (v.Date between @beginDate and @endDate)
and v.valid = 1
group by v.MeasureId KH

---

thanks for all your replies, most of them seemed to work but it took too much time to run on my database (+1minute on 90E6 records). I decided to put it in a SP and use table datatypes to calculate the result I want in three loops. I got it down to 6 seconds

---

Can you post the method you used here ? +1 mins to 6 secs is great improvement
KH

---

The Query you wrote is correct, but as per my idea please check the datatypes of that particular "Valid" column, if datatype varies convert it into the integer and re-execute the query.
Thanks
Sandeep Boggavarapu

---

Ok getting it down to 6s is not entirely correct cause I forgot I filtered on some things in the where close. I ran it unfiltered and it took me exactly 1m. It has some more columns too than just the MeasureId and the count. Here’s a quick overview of what I’m trying to do. I’m working for a governmental institution in Belgium which monitors radiological activity in the country. We have a measuring station each 20km which sends us data on a 10minute basis. We have a total of about 220 measuring stations all over the country. One measuring station (we call it "balise", it’s a french word) has one or more measures (example of a measure is "doserate", that is a radioactive measure. Another measure could be "rain", cause most of these stations have rain detectors on them too).
A measure has a value (from the table MinuteValues) each 10m, so basically a value has a foreign key to a measure. declare @beginDate datetime
declare @endDate datetime
declare @baliseId int
declare @countValues int set @beginDate = ’01/12/2006′
set @endDate = ’01/01/2007′
declare @values table
(
BaliseId int,
[Value] real,
Valid bit
) declare @balises table
(
BaliseId int,
NumValues int,
ExpectedValues int,
Percentage float
) insert into @values
SELECT Balises.Id, MinuteValues.Value, MinuteValues.Valid
FROM Balises INNER JOIN
Measures ON Balises.Id = Measures.BaliseId INNER JOIN
MinuteValues ON Measures.Id = MinuteValues.MeasureId
WHERE (MinuteValues.Date >= @beginDate AND MinuteValues.Date < @endDate)
order by Balises.id asc insert into @balises(BaliseId)
select distinct BaliseId from @values order by BaliseId asc declare @curs cursor set @curs = cursor for
select BaliseId from @balises open @curs
fetch next from @curs into @baliseId
while @@fetch_status = 0
begin
select @countValues = count(*)
from @values
where BaliseId = @baliseId
and valid = 1
and value is not null update @balises
set NumValues = @countValues,
ExpectedValues = (datediff(minute,@beginDate,@endDate) / 10),
Percentage = round(cast((@countValues / (datediff(minute,@beginDate,@endDate) / 10.0) * 100) as float),3)
where BaliseId = @baliseId fetch next from @curs into @baliseId
end
close @curs
deallocate @curs select * from @balises

---

]]>