How the COUNT function works in SQL
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 (...)]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| 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