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;

• Zach Smith: I've found in a different query that when I'm trying to aggregate on several different values, that null is not being aggregated. I would like to know how many null values there are alongside other value counts
• user1822: @ZachSmith: why do you use where cola is null then?
• Zach Smith: That is just a simplification of my query. I wasn't aware that it behaved like that and was just testing. COUNT(*) will count combinations of all columns within a row. I don't want to do this..
• user1822: @ZachSmith: count(*) will not count "combinations". It will count rows in the group because the * by definition is never null.
• ypercubeᵀᴹ: @LightnessRacesinOrbitb true but the SQL standard refers to them as "null values".

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

• ypercubeᵀᴹ: Chat room for discussion of this answer

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]')
;

• Hannah Vernon: This doesn't seem to answer the question. Can you clarify?

Answer 4

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

• ypercubeᵀᴹ: Seems like you fell in the very trap you describe. NULL != NULL does not evaluate to TRUE (as you seem to imply). It evaluates to UNKNOWN ;)