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

Question

A SELECT statement returns several rows:

SELECT
  ColA
FROM
  TableA
WHERE
  ColA IS NULL

I get 47 rows that have 'NULL' for ColA within TableA.

ColA
NULL
NULL
NULL
etc...

If I add an aggregate to this query:

SELECT
  ColA,
  COUNT(ColA) AS theCount
FROM
  TableA
WHERE
  ColA IS NULL
GROUP BY ColA

I get

ColA | theCount
NULL | 0

Why is this happening, and what can I do to avoid this?

Answer 1

Aggregate functions ignore null values.

So

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;

Answer 2

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(*).

Answer 3

NULL is not empty, it's UNKNOWN, NULL != NULL, ANYTHING != NULL ,so count(NULL) =0