luke luke - 1 year ago 88
SQL Question

Does DISTINCT rule out Nulls

When using COUNT (DISTINCT fieldA), does it rule out counting NULL values that would be in fieldA

Answer Source

You Kind of have 2 questions between your title and your narrative.

DISTINCT does NOT eliminate (rule out) Nulls


Aggregate Functions IGNORE Null Values

As others have mentioned so if you want to count all NON NULL DISTINCT Values use the code you mentioned.


If you want to count all nulls as another value you can do that 1 of 2 ways.

1) Use COALESCE() to eliminate the null with a value that is not represented within your dataset. E.g.

 SELECT COUNT(DISTINCT COALESCE(columnName,'|||||||||'))

2) the more certain way use conditional aggregation similar to what Gordon showed:

To show how distinct does not eliminate null values:

CREATE TABLE DistinctTest (Col INT)
INSERT INTO DistinctTest (Col) VALUES (NULL),(1),(2),(3),(NULL)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download