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

Convert the null values to some other text (blank or '[NULL]') and count those.

You can Use either if null or coalesce to change the null value. Just be sure to change the null to some other text that does not exist.

Example 1: Using ifnull and converting null to blank:

select
       ColA
      ,count(ifnull(ColA,'')) as theCount
  from tablea
 group by 1
;

Example 2: Using coalesce and converting null to text '[NULL]':

select
       ColA
      ,count(coalesce(ColA,'[NULL]')) as theCount
  from tablea
 group by coalesce(ColA,'[NULL]')
;

Answer 4

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