Count Distinct - it's counting blanks

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?

Answers

  • 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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • @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/

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"