count only null values in SqlserverCe3.5

Question

I'm working on asp.net website. I'm not able to count the only null values for particular id from the database Sqlserverce. suppose I have table called Alarm

alarmid     date
1   12/12/2010  
2   Null
3   4/4/2010
5   12/05/2011
6   Null

Here I need to find count of null values. ie 2. I used ISNULL with COUNT function but its not working. How to find the null values count. I tried like select count(date) where date is null. but not working. pls help me out.

score 1 · Accepted Answer · answered Sep 08 '11 at 05:07

1

count(Null) isn't supposed to count anything, but you can give the count function something else. For example, 1

select 
count(1) 
where date is null.

answered Sep 08 '11 at 05:07

SingleNegationElimination

151,563
33
264
304

Ah much more elegant than mine :) – Justin King Sep 08 '11 at 05:15

Justin King · Answer 2 · 2011-09-08T05:14:35.623

0

In SQL Server you can do this hopefully it would work in CE

SELECT count(isnull([date],0)) as total

Update: This works in CE, above only works in SQL Server as isnull not supported in CE

select count(COALESCE(name ,1)) from test where name is null

edited Sep 08 '11 at 05:14

answered Sep 08 '11 at 05:03

Justin King

1,428
12
14

thanks justin but it giving total count but I want only null values count. – Ram Sep 08 '11 at 05:07

count only null values in SqlserverCe3.5

2 Answers2