count the number of null values in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

count the number of null values in a column

Author

Topic

archanasql
Starting Member

27 Posts

Posted - 2008-02-03 : 13:21:36

Hi,

I have a table employee with 4 columns,

empno fname lname deptno

1 abc def 10
2 fff hhh 20
3 abc def NULL
4 abc def NULL
5 abc def 50

suppose i want to know the total number of null values in a particular column say deptno how shuld i write a query?

select count(deptno) from employee
where deptno IS NULL..

When i query this i get the result as 0..

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-03 : 13:25:58

Tried this:

select count(*) from employee
where deptno IS NULL..

archanasql
Starting Member

27 Posts

Posted - 2008-02-03 : 13:35:49

Yes, this works...

But what is the difference between the two queries...bcoz i used count(columnname) instead of count(*).. i suppose the earlier one also should work right?

subrata4allfriends
Starting Member

24 Posts

Posted - 2008-02-03 : 13:51:30

query1:"select count(deptno) from employee
where deptno IS NULL"

query2:"select count(*) from employee
where deptno IS NULL"

In first query it returns no value to count(because of deptno is null)........that's why 0 is resulting whereas in the second query its getting value

Thanks & Regards,
4allfriends.

"Life is not a bed of roses."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-03 : 17:16:34

count(ColumnName) doesn't count the nulls in the column

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-03 : 23:03:48

If you really want to do something similar, you can use this too

SELECT SUM(CASE WHEN ColumnName IS NULL 1 ELSE 0 END) FROM Table

rahulmalhotra26
Starting Member

23 Posts

Posted - 2008-02-03 : 23:12:05

you know how it works why did you post your topic again

Rahul

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-04 : 01:26:02

select count(*)-count(col) from table

Madhivanan

Failing to plan is Planning to fail