Why does COUNT() aggregate return 0 for 'NULL'?

Aggregate functions ignore null values.

SELECT COUNT(cola) AS thecount
FROM tablea

is equivalent to

SELECT count(*) AS thecount
FROM tablea
WHERE cola IS NOT NULL;

As all of your values are null, count(cola) has to return zero.

If you want to count the rows that are null, you need count(*)

SELECT cola,
       count(*) AS theCount
FROM tablea
WHERE cola is null
GROUP BY cola;

Or simpler:

SELECT count(*) AS theCount
FROM tablea
WHERE cola is null;

If you want to count NULL and NOT NULL values in a single query, use:

SELECT count(cola) as not_null_count, 
       count(case when cola is null then 1 end) as null_count
FROM tablea;

This is by design.

COUNT(<expression>) counts rows where the <expression> is not null.

COUNT(*) counts rows.

So, if you want to count rows, use COUNT(*).

Related