COUNT DISTINCT

Grouped COUNT DISTINCT

Each group gets its own distinct count.

highlighted = computed this step

Grouped count distinct

With GROUP BY, each group gets its own distinct-value buckets.

per group buckets\text{per group buckets}

Read grouped output

The compiler forms 3 groups and outputs 3 count rows.

groups=3\text{groups}=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.

Grouped COUNT DISTINCT: input rowsidregionbuyer1eastann2eastann3eastNULL4eastbea5westcy6westcy7northNULL8northNULL group bucketskeysourceRowsrowCount[east][0, 1, 2, 3]4[west][4, 5]2[north][6, 7]2 distinct non-NULL value bucketskeydistinctValuesvalueSourcesnullSourceRowsduplicatesFoldedcountDistinct[east][ann, bea][{value:ann, sourceRows:[0, 1]}, {value:bea, sourceRows:[3]}][2]12[west][cy][{value:cy, sourceRows:[4, 5]}][]11[north][][][6, 7]00 COUNT DISTINCT output rowsregiondistinct_counteast2west1north0 COUNT DISTINCT factsfactvalueinputRowCount8groupCount3outputRowCount3valueColumnbuyernullSkipped3duplicateValuesFolded2allNullGroups1groupOrderfirst_seen_source_order

Groups do not share buckets

A value repeated inside one group does not affect another group's count.

separate group buckets\text{separate group buckets}

Summary

Grouping happens before each group's distinct count.

group then count\text{group then count}