COUNT DISTINCT

All-NULL Group Counts Zero

A group with no non-NULL value has count zero.

highlighted = computed this step

All-NULL group counts zero

If a group has only NULL cells in the counted column, it has no distinct non-NULL bucket.

all NULL gives zero\text{all NULL gives zero}

Read the zero

The all-NULL group count is 0, and the compiler marks 1 all-NULL group.

count=0\text{count}=0

COUNT DISTINCT examples are tiny finite-table transforms; SQL dialect completeness, collations, type coercion, optimizer behavior, execution cost, indexing, and database-product claims are out of scope.

All-NULL group counts zero: input rowsidregionbuyer1northNULL2northNULL group bucketskeysourceRowsrowCount[north][0, 1]2 distinct non-NULL value bucketskeydistinctValuesvalueSourcesnullSourceRowsduplicatesFoldedcountDistinct[north][][][0, 1]00 COUNT DISTINCT output rowsregiondistinct_countnorth0 COUNT DISTINCT factsfactvalueinputRowCount2groupCount1outputRowCount1valueColumnbuyernullSkipped2duplicateValuesFolded0allNullGroups1groupOrderfirst_seen_source_order

Zero is explicit

This zero comes from an empty distinct non-NULL bucket list.

empty bucket list\text{empty bucket list}

Summary

All-NULL does not invent a NULL bucket to count.

NULL not a counted bucket\text{NULL not a counted bucket}