11

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?

0
30

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;
9
  • 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
    – Zach Smith
    Oct 28 '16 at 9:50
  • 1
    @ZachSmith: why do you use where cola is null then? Oct 28 '16 at 9:51
  • 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..
    – Zach Smith
    Oct 28 '16 at 9:53
  • 3
    @ZachSmith: count(*) will not count "combinations". It will count rows in the group because the * by definition is never null. Oct 28 '16 at 9:54
  • 1
    @LightnessRacesinOrbitb true but the SQL standard refers to them as "null values". Oct 29 '16 at 23:15
20

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

1
-1

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]')
;
1
  • This doesn't seem to answer the question. Can you clarify?
    – Hannah Vernon
    Nov 27 '20 at 6:56
-3

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

1
  • 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 ;) May 2 '19 at 6:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.