Count distinct values in MySQL without folding NULL values

aggregate-functions, count, mysql, null, sql

Solution

Use `NOT EXISTS` in a `WHERE` clause:

SELECT count(*)
FROM   a
WHERE  NOT EXISTS (SELECT * FROM a a2 WHERE a2.f = a.f AND a2.id < a.id);

This way you can also get actual rows - if you need more than the bare count:

SELECT *
FROM   a
WHERE  NOT EXISTS (SELECT * FROM a a2 WHERE a2.f = a.f AND a2.id < a.id)

The `=` operator makes sure that all rows with `f IS NULL` are included. You had that in your query already.

-> sqlfiddle

Neither of these would work:

SELECT DISTINCT f FROM a;

SELECT * FROM a GROUP BY f;

.. because both would also fold `NULL` values, and you want

every record WHERE f IS NULL.

Problem

I have table `a` with two fields: `id` (PK) and `f`. Consider following records: ``` id | f 1 | NULL 2 | 'foo' 3 | 'bar' 4 | NULL 5 | 'foo' 6 | 'baz' ``` I want to retrieve and count all the records having distinct `f` values including every record `WHERE f IS NULL`. Given this criteria, the query should return every record except #5, because the same value is already included in the set, and the total count would be 5. The query I'm using to retrieve all records looks like this: `SELECT CASE WHEN EXISTS (SELECT id FROM a a2 WHERE a2.f = a.f AND a.id < a2.id) THEN 1 END AS not_distinct FROM a HAVING not_distinct IS NULL` If this query could be improved, I'd welcome any feedback. Anyway, the main problem is counting. Obviously adding a `COUNT(*)` will not help here and I'm totally lost how to count the records after the filtering.

Original source