March 5, 2014 at 6:07 am
why the below query showing zero even if there
are 2 records in age column with null value
select count(age) from dbo.prim
where age is null
while the same table queried with
the below query works
giving 2 records
select count(name) from dbo.prim
where age is null
March 5, 2014 at 6:17 am
According to BOL (emphasis is mine):
COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.
ALL is the default.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 5, 2014 at 6:19 am
When you look at your messages window you'll notice the message "Warning: Null value is eliminated by an aggregate or other SET operation." which will explain your situation.
It is because you are counting the item in the column containing the NULL values. These NULL values will (default) be eliminated in the count. To get the desired results you can count on another column or all columns. See the example below:
create table #test (id int, value int)
insert into #test
select 1,1
union all select 2,null
union all select 3,null
union all select 4,2
select
count(value) as total_NULL_column
, count(ID) as total_ID_column
, count(*) as total_all_columns
from #test
where value is null
drop table #test
March 5, 2014 at 6:27 am
If you want to count the number of rows where the age is null, you probably want this:
select count(*) from dbo.prim
where age is null
Count(*) means count the number of rows. Count(<column name>) means count the number of rows where that column has a non-null value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply