SQL Tutorials
Understanding FROM
Understanding JOIN
Understanding WHERE
Using dates in SQL
Using strings in SQL
SQL Functions
CASE
COUNT
BigQuery Tutorials
Arrays Explained
Data Types
Dates and Times
String Functions Explained
Window Functions Explained
BigQuery Functions
ARRAY_AGG
CASE
CAST
COALESCE
CONCAT
COUNT [DISTINCT]
CURRENT_DATE
CURRENT_DATETIME
CURRENT_TIMESTAMP
DATE_DIFF
DATE_TRUNC
DATETIME_DIFF
DATETIME_TRUNC
EXTRACT
IF
MEDIAN
SUBSTR
TIME_DIFF
TIME_TRUNC
TIMESTAMP_DIFF
TIMESTAMP_TRUNC
UNNEST
Snowflake Tutorials
Histograms
Linear Regression
Moving averages
Pivot Tables
Running Totals
Summary Statistics
Window Functions
SQL Server Tutorials
Data Types
Dates and Times
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

Become a SQL Expert
Get five free SQL lessons from Count's Head of Data, Taylor Brownlow.
Email
Title