Count distinct values in MySQL without folding NULL values

Question

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.

@AjithSasidharan: The question is already clear on that: 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. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 28, 2012 at 13:14

Community · Accepted Answer · 2020-06-20 09:12:55Z

16

There's a pretty simple approach that might work for you:

select count(distinct ifnull(f, id))
from a

Note that this query assumes that f values are never id values, and based on sample data and experience this is reasonable.

Edited:

I thought about it and there's an even simpler approach:

select count(distinct f) + sum(f is null) from a;

which you can see running on sqlfiddle

This works because distinct throws away nulls, and sum(condition) counts the number of times condition is true because in mysql true is 1 and false is 0.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 28, 2012 at 13:18

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

package Over a year ago

Although your solution generally is better performance-wise, I chose Erwin's answer since it suits my needs better in current situation. Thanks anyway!

Bohemian Over a year ago

@package See edited answer for another pretty simple approach that will perform well too, but is less "smarty pants". (btw, the answer you selected will always suck performance wise).

Erwin Brandstetter · Accepted Answer · 2012-11-28 13:09:52Z

4

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.

edited Nov 28, 2012 at 13:09

answered Nov 28, 2012 at 12:51

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

2 Comments

package Over a year ago

This solution seems so obvious I wonder why I haven't figured it out myself. Thanks a lot, works beautifully.

Erwin Brandstetter Over a year ago

That's the thing with solutions: most seem so obvious once you have them. :)

Limitless isa · Accepted Answer · 2016-05-18 14:16:58Z

0

    UPDATE comments SET view =     
                              (SELECT COUNT(DISTINCT browser, ip, ifnull(user_id, 0)) 
                              FROM counters WHERE item_type=comments.item_type )
    WHERE id=1

answered May 18, 2016 at 14:16

Limitless isa

3,82238 silver badges29 bronze badges

Collectives™ on Stack Overflow

Count distinct values in MySQL without folding NULL values

3 Answers 3

Edited:

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Edited:

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest