COUNT [DISTINCT]
Definition
The COUNT function returns the number of rows in a SQL expression.
Syntax
COUNT(*) [OVER (...)]
COUNT([DISTINCT] expression) [OVER (...)]COUNT(*) counts the number of rows in the input.COUNT(expression) returns the number of rows in the expression other than NULL.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 xThe example above shows three different ways COUNT can be used.
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
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 xPractical Info
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.