COUNT(DISTINCT): Calculating the Count of Distinct Values in a Field or Expression
The COUNT(DISTINCT) function calculates the count of the distinct values in a field or expression, over a group of rows, determined by a partition.
Calculate the Count of Distinct Values in a Field or Expression
COUNT(DISTINCT field) OVER([PARTITION BY part1[, part2, ...]])
where:
field
Is the name of the input field or expression.
PARTITION BY part1, part2, ...
Are partitioning columns or expressions.
Calculating the Count of Distinct Values in a Field or Expression
The following syntax calculates the count of distinct values of the UNITS field for each region and state.
COUNT(DISTINCT UNITS) OVER(PARTITION BY REGION, ST)