SQL Resources/SQL/COUNT

COUNT

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.
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
all_rows
5
non_null
4
unique_non_null
3

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 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
x
NULL
1
2
2
5
count_all_rows
1
1
2
2
1
count_x
0
1
2
2
1
count_unique_x
0
1
1
1
1

Share SQL code
Write SQL with your team in real-time.
Share SQL code
Write SQL with your team in real-time.