Counting the number of NULLs in a SQL Server Column-CodePudding

I have two queries like so:

SELECT MyId, MyColumn FROM MyTable WHERE MyColumn IS NULL;
SELECT count(MyColumn) as MyCount FROM MyTable WHERE MyColumn IS NULL;

The results I get are:

MyId    MyColumn
10      NULL

Why is the count 0 always in the second query?

CodePudding user response：

The COUNT() function ignores NULL values, and so the count in your second query will always be zero. Either count something else:

SELECT COUNT(*) AS MyCount
FROM MyTable
WHERE MyColumn IS NULL;

Or else count over the entire table using a CASE expression to explicitly count NULL values:

SELECT COUNT(CASE WHEN MyColumn IS NULL THEN 1 END) AS MyCount
FROM MyTable;

CodePudding user response：

Count doesn't count null. You need to do something like this, transform null to 1 then sum them:

SELECT SUM(CASE WHEN MyColumn IS NULL THEN 1 ELSE 0 END) AS count_nulls
FROM MyTable;

CodePudding user response：

You can simply use count(1) rather than column name in the count function as it ignores null value which counting.

SELECT COUNT(1) AS MyCount
FROM MyTable
WHERE MyColumn IS NULL;