COUNT DISTINCT
Description
This function calculates the number of distinct elements per group. COUNT DISTINCT can be applied on any data type.
Syntax
COUNT ( DISTINCT table.column )
NULL handling
NULL values are not counted. If all the values of a group are NULL, the result for this group is 0.
COUNT DISTINCT over multiple columns
UNIQUE_ID can be used inside COUNT DISTINCT to count the number of unique combinations of values from different columns.
Examples
[1] Count distinct elements of the column: | |||||||||||
| |||||||||||
|
[2] Count the number of distinct values grouped by country: | ||||||||||||||||||||||||||
| ||||||||||||||||||||||||||
|
[3] Count the distinct number of values mixed with nulls, grouped by country: | ||||||||||||||||||
| ||||||||||||||||||
|
[4] UNIQUE_ID can be used to count the distinct number of unique combinations of values from different columns. This example shows a list of six vendors together with the city and country of their headquarters. We want to count the number of different cities our vendors come from. Since just the city names are not unique, it is required to count unique combinations of city name and country. We can use | |||||||||||||||||||||||||||
| |||||||||||||||||||||||||||
|