Why aren't nulls counted in COUNT(columnname)

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

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

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