Count Distinct - it's counting blanks

ekola

Hi - I'm using COUNT (DISTINCT to get a unique count of ID numbers and there are some records where the ID number is intentionally blank. I want these records to be excluded from the count entirely but this function seems to be counting 'blank' as its own unique value and is therefore adding 1 to the unique count which is throwing everything off. How can I exclude blanks from this count?

MarkSnodgrass

I'm assuming you are doing this at the card level. You can do this a couple different ways.

1. If you don't need the blank records, drag the ID field into the filter section and tell it to exclude blank records. Then, your count distinct should work as is.
2. If you need the blanks but don't want to count them you can write a beast mode like this:

SUM(CASE WHEN TRIM(ID) = '' OR ID IS NULL THEN 0 ELSE 1 END)

Hope this helps.

jaeW_at_Onyx

@MarkSnodgrass is pointing out that an empty string, '' <> NULL. hence his test:

TRIM(ID) = '' or ID is null

HOWEVER, SUM ... 1 else 0 END will give you COUNT not COUNT (DISTINCT).

@ekola ,

you should validate whether COUNT DISTINCT is including NULLS or not, because standard behavior in many SQL implementations is to IGNORE NULLS with COUNT DISTINCT. But again... NULL <> ''

https://bertwagner.com/posts/count-distinct-and-nulls/