luke luke - 2 months ago 13
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

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

DISTINCT does NOT eliminate (rule out) Nulls

However

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.

SELECT COUNT(DISTINCT columnName)

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)

SELECT DISTINCT  *
FROM
    DistinctTest