SQL Resources/SQL/COUNT# COUNT

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

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