COUNT

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.

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

COUNT(*) [OVER (...)]
COUNT([DISTINCT] expression) [OVER (...)]

• 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 sometimes be used as a window function. The below example runs the COUNT function for each value of x.

with my_numbers as (
  select 1 as x union all
  select 2 as x union all
  select 2 as x union all
  select 5 as x union all
  select null as x 
)
select
  count(*) as all_rows,
  count(x) as non_null,
  count(distinct x) as unique_non_null
from
  my_numbers

with my_numbers as (
  select 1 as x union all
  select 2 as x union all
  select 2 as x union all
  select 5 as x union all
  select null as 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
  my_numbers