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 x
The 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 x
Practical 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
.