COUNT [DISTINCT]

The COUNT function returns the number of rows in a SQL expression.

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

• 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).

• The data type of the output is INT64.

• Window Functions Explained