5

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?

25

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;
  • 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? – a_horse_with_no_name 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. – a_horse_with_no_name Oct 28 '16 at 9:54
  • 1
    @LightnessRacesinOrbitb true but the SQL standard refers to them as "null values". – ypercubeᵀᴹ Oct 29 '16 at 23:15
18

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

-3

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

  • 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 ;) – ypercubeᵀᴹ May 2 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.