SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='count-distinct'

COUNT [DISTINCT]

Definition

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_rowscount_xcount_all_unique_values_of_x
543

The example above shows three different ways COUNT can be used.

Loading code...
  • 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.

Loading code...
xcount_all_rowscount_xcount_unique_x
null100
1111
2221
2221
5111

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.
  • Window Functions Explained
Loading code...