COUNT DISTINCT

Count Result

The result is the number of distinct non-NULL buckets.

highlighted = computed this step

Count result

After NULLs are skipped and duplicates fold, the result is the bucket count.

COUNT DISTINCT result\text{COUNT DISTINCT result}

Read the result

From 8 input rows, the distinct buyer count is 3.

distinct count=3\text{distinct count}=3

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.

Count result: input rowsidregionbuyer1eastann2eastann3eastNULL4eastbea5westcy6westcy7northNULL8northNULL group bucketskeysourceRowsrowCount[all_rows][0, 1, 2, 3, 4, 5, 6, 7]8 distinct non-NULL value bucketskeydistinctValuesvalueSourcesnullSourceRowsduplicatesFoldedcountDistinct[all_rows][ann, bea, cy][{value:ann, sourceRows:[0, 1]}, {value:bea, sourceRows:[3]}, {value:cy, sourceRows:[4, 5]}][2, 6, 7]23 COUNT DISTINCT output rowsdistinct_count3 COUNT DISTINCT factsfactvalueinputRowCount8groupCount1outputRowCount1valueColumnbuyernullSkipped3duplicateValuesFolded2allNullGroups0groupOrderfirst_seen_source_order

Not row count

The distinct count is smaller than the input row count because repeats and NULLs do not add buckets.

not all rows\text{not all rows}

Summary

COUNT DISTINCT is a value-bucket count.

bucket count\text{bucket count}