Why aren't nulls counted in COUNT(columnname)

Question

So I came across something the other day at work, trying to count how many null values after running an import.

So, I did:

select COUNT(columnname) from table 
WHERE ColumnName is null

Which didn't count the nulls...

Then I did,

select COUNT(*) from table 
WHERE ColumnName is null

Which gave me the count.

So, something that bugged me is why exactly this doesn't count the null values.

I have looked at this question (along with a good search around Google...): In SQL, what's the difference between count(column) and count(*)?, and whilst it tells me that COUNT(columnname) doesn't count nulls, I would like to know exactly why nulls aren't counted using this method?

Many Thanks, James.

Solution 1

COUNT counts values, since null is not a value it does not get counted.

If you want to count all null values you could do something like this:

SELECT COUNT(ID) as NotNull, SUM(CASE WHEN ID IS NULL then 1 else 0 end) as NullCount

Solution 2

Why aren't nulls counted in COUNT(columnname)?

COUNT(*)

will count all rows

COUNT(columnname)

will count all rows, except those rows where columnname IS NULL.

And what's the reason? It's just that the COUNT() function is designed to work this way: NULL values are treated differently from other values, because NULL can be considered as a placeholder for "unknown" values, so it is very common that you just want to count rows that have a real value and skip rows that don't have.

Counting the rows that don't have a value is less common, and SQL doesn't provide a function for it. But you can calculate it easily:

SELECT
  COUNT(*) As rows,
  COUNT(columnname) AS non_null_count,
  COUNT(*) - COUNT(columnname) AS null_count
FROM
  yourtable

Solution 3

If you instead do count(1) you wont be affected by this the filter what to count in the condition.

Solution 4

COUNT counts only real values...null is not a value. So:

COUNT(*) is used when you want to include the null-able values.

If you just want to count the number of non-null-able values, you would use COUNT(columnname)

View more solutions

Comments

[James Hatton]

THanks for the answer.

[James Hatton]

This is what I thought, that it was because it doesn't have a value. Thanks for the answer!

[Dan H]

This should be way up. Works great for simple group bys when you just want to count a total of items, including nulls.