COUNT DISTINCT function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The COUNT function returns the number of rows in a SQL expression.
The COUNT function returns the number of rows in a SQL expression.
COUNT(*) counts the number of rows in the input.COUNT(expression) returns the number of rows in the expression other than NULL.| count_all_rows | count_x | count_all_unique_values_of_x |
|---|---|---|
| 5 | 4 | 3 |
The example above shows three different ways COUNT can be used.
COUNT(*) [OVER (...)]
COUNT([DISTINCT] expression) [OVER (...)]COUNT(*) counts the number of rows in the table x.COUNT(x) counts the number of elements in the table x excluding NULL values.COUNT(DISTINCT x) counts the number of unique elements in the table x, excluding NULL values.COUNT can also be used as a window function. The below example runs the count function for each value of x.
SELECT
COUNT(*) AS count_all_rows,
COUNT(x) AS count_x,
COUNT(DISTINCT x) AS count_all_unique_values_of_x
FROM
UNNEST([1, 2, 2, 5, NULL]) AS x| x | count_all_rows | count_x | count_unique_x |
|---|---|---|---|
| null | 1 | 0 | 0 |
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 1 |
| 2 | 2 | 2 | 1 |
| 5 | 1 | 1 | 1 |
expression can be any data type, but if DISTINCT is used then the data type needs to be groupable (all types other than ARRAY, STRUCT and GEOGRAPHY). INT64. SELECT
x,
COUNT(*) OVER (PARTITION BY x) AS count_all_rows,
COUNT(x) OVER (PARTITION BY x) AS count_x,
COUNT(DISTINCT x) OVER (PARTITION BY x) AS count_unique_x
FROM
UNNEST([1, 2, 2, 5, NULL]) AS x