SELECT knowledge FROM sql_resources WHERE category='sql' AND slug='count'

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.

all_rows	non_null	unique_non_null
5	4	3

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

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

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

Loading code...

x	count_all_rows	count_x	count_unique_x
null	1	0	0
1	1	1	1
2	2	2	1
2	2	2	1
5	1	1	1

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

Loading code...