SQL Resources/BigQuery/COUNT [DISTINCT]

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
count_all_rows
5
count_x
4
count_all_unique_values_of_x
3

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
x
NULL
1
2
2
5
count_all_rows
1
1
2
2
1
count_x
0
1
2
2
1
count_unique_x
0
1
1
1
1

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).
  • The data type of the output is INT64.

Related Pages

Try a better way to write SQL in BigQuery
Write SQL together with Count.
Try a better way to write SQL in BigQuery
Write SQL together with Count.