CODEWITHSUNDEEP
sqldatabasesql-server-2008t-sqlcount
Fahd
Fahd

Reputation: 266

TSQL Count function and distinct missing NULL values

According to MSDN, the TSQL COUNT(*) function includes any NULL values in the result unless DISTINCT is also used (source: http://msdn.microsoft.com/en-us/library/ms175997.aspx)

However, in my query NULL values are being ignored. To test this, I created a small table and filled it with some data:

CREATE TABLE tbl (colA varchar(1), colB varchar(10))
INSERT tbl VALUES
('Y', 'test1'),
('Y', 'test2'),
(null, 'test3'),
(null, 'test4'),
('N', 'test5')

I then ran the following 2 queries on it: 

SELECT count(*) FROM tbl
WHERE colA <> 'N'

and 

SELECT DISTINCT colA FROM tbl
WHERE colA <> 'N'

Both results ignore the NULL values. I get 2 and 'Y' as the results respectively. I'm at a loss to why this is happening. Could someone please clue me in?

Results simulated in SQL Fiddle: http://sqlfiddle.com/#!3/8f00b/9

Upvotes: 2

Views: 5964

Answers (6)

user2126738
user2126738

Reputation: 21

 select count(IsNull(colA,'')) as colA, count(colB) as colB from @tbl

should also do the trick

Upvotes: 2

Robert
Robert

Reputation: 25753

You can also use ISNULL function:

SELECT count(*) 
FROM tbl
WHERE isnull(colA,'F') <> 'N'

MSDN: http://msdn.microsoft.com/en-us/library/aa933210(v=sql.80).aspx

Upvotes: 1

HABO
HABO

Reputation: 15816

declare @tbl as Table (colA varchar(1), colB varchar(10)) 
insert @tbl values 
  ('Y', 'test1'), ('Y', 'test2'), (null, 'test3'), (null, 'test4'), ('N', 'test5')
select * from @tbl

select
  count(*) as 'Rows', -- All rows.
  count(colA) as [colA Values], -- Rows where   colA   is not NULL.
  count(colB) as [colB Values],  -- Rows where   colB   is not NULL.
  count(distinct colA) as [Distinct colA], -- Number of distinct values in   colA .
  count(distinct colB) as [Distinct colB], -- Number of distinct values in   colB .
  -- NULL never equals anything, including another NULL.
  case when 42 = NULL then 'Yes' else 'Unknown' end as [Answer Is NULL?],
  case when NULL = NULL then 'Yes' else 'Unknown' end as [NULL = NULL],
  -- Use   is NULL   to explicitly check for   NULL .
  case when NULL is NULL then 'Yes' else 'Unknown' end as [NULL is NULL]
  from @tbl

Upvotes: 2

John Woo
John Woo

Reputation: 263743

Because NULL is unknown, the server doesn't know what is the value for it. But try using IS NULL

SELECT count(*) 
FROM tbl
WHERE colA <> 'N' or  
      colA IS NULL

SQLFiddle Demo

Upvotes: 2

Ann L.
Ann L.

Reputation: 13965

Any comparison to Null other than IS NULL will fail. So both Null = 'N' and Null <> 'N' return false.

If you want the nulls included, you need to say 

WHERE colA <> 'N' or colA IS NULL

Upvotes: 2

Blorgbeard
Blorgbeard

Reputation: 103467

Nulls are weird.

null <> 'N' evaluates to false.
null = 'N' also evaluates to false.

You need to handle null explicitly: 

SELECT count(*) FROM tbl
WHERE (colA <> 'N') or (colA is null)

Upvotes: 5

Related Questions