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

    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
    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.
  • Related Pages

  • Window Functions Explained
  • Subscribe to newsletter

    Subscribe to receive the latest blog posts to your inbox every week.

    By subscribing you agree to our Privacy Policy.
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.

    Start solving your organization's biggest problems with Count today.

    Stay up to date with all things count, data and problem solving.