COUNT

Reference material for COUNT

Counts the number of rows or not NULL values.

Syntax

COUNT([ DISTINCT ] <expression>) [FILTER ([WHERE] <condition>)]

Parameters

Parameter	Description	Supported input types
<expression>	The expression to count	Any
<condition>	An optional boolean expression to filter rows used in aggregation	BOOL

Valid values for the input expression include column names or functions that return a column name. When DISTINCT is being used, only the unique number of rows with no NULL values are counted. COUNT(*) returns a total count of all rows in the table, while COUNT(<column_name>) returns a count of non-null rows in the specified <column_name>.

By default, COUNT(DISTINCT) returns exact results. If you do not require a precise result and want to have faster performance, consider using the APPROX_COUNT_DISTINCT function. See below for examples and considerations.

Return Type

NUMERIC

Examples

The tournaments table contains the following data:

SELECT name, totalprizedollars FROM tournaments ORDER BY name LIMIT 5;

name text null	totalprizedollars int null
The Acceleration Championship	19274
The Acceleration Trials	13877
The Accelerator Cup	19466
The African Grand Prix	895
The Alpine Adventure Rally	16810

Rows: 5Execution time: 6.00ms

The following example counts the total number of tournament names in the tournaments table. COUNT ignores NULL values:

SELECT COUNT(name) FROM tournaments;

count long
157

Rows: 1Execution time: 6.27ms

COUNT(DISTINCT) counts only unique non-NULL values, skipping duplicates. The levels table has 10 rows but only 3 distinct leveltype values (FastestLap, FirstToComplete, and Drift):

SELECT COUNT(leveltype) AS total, COUNT(DISTINCT leveltype) AS distinct_count FROM levels;

total long	distinct_count long
10	3

Rows: 1Execution time: 6.00ms

For large datasets, APPROX_COUNT_DISTINCT offers faster performance at the cost of a small estimation error. The following example uses 50,000 unique values to show the approximation tradeoff:

SELECT APPROX_COUNT_DISTINCT(number) AS approx, COUNT(DISTINCT number) AS exact
FROM generate_series(1, 50000) AS t(number);

approx long	exact long
50160	50000

Rows: 1Execution time: 7.11ms