COUNT

Definition

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

Syntax

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

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

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

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