COUNT

The COUNT function returns the count of numeric values, or only the distinct values.

Syntax

The following is the correct syntax for using the COUNT function as an aggregate:

COUNT( { [ DISTINCT ] expr | * } ) --> BIGINT

The following is the correct syntax for using the COUNT function as a window function:

COUNT ( { [ DISTINCT ] expr | * } ) OVER (
         [ PARTITION BY value_expression [, ...] ]
         [ ORDER BY value_expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
         [ frame_clause ]
      )

Arguments

The following table describes the COUNT arguments:

Parameter

Description

expr

Any value expression

*

Specifies that COUNT should count all rows. It does not use information about any particular column, and preserves duplicate rows and NULL values.

DISTINCT

Specifies that the operation should operate only on unique values

Returns

  • The COUNT function returns BIGINT.

Notes

The following notes apply to the COUNT function:

  • When all rows contain NULL values, the function returns NULL.

  • COUNT(*) returns the number of items in a group, including duplicates and NULL values.

  • COUNT(ALL expression) evaluates expressions for each row in a group, returning the number of non-null values.

  • COUNT(DISTINCT expression) evaluates expressions for each row in a group, returning the number of unique, non-null values.

Examples

The examples in this section are based on a table named nba, structured as follows:

CREATE TABLE nba
(
   Name varchar(40),
   Team varchar(40),
   Number tinyint,
   Position varchar(2),
   Age tinyint,
   Height varchar(4),
   Weight real,
   College varchar(40),
   Salary float
 );

The following table is a preview of the source nba.csv table shown below:

nba.csv

Avery Bradley

Boston Celtics

0

PG

25

2-Jun

180

Texas

7730337

Jae Crowder

Boston Celtics

99

SF

25

6-Jun

235

Marquette

6796117

John Holland

Boston Celtics

30

SG

27

5-Jun

205

Boston University

R.J. Hunter

Boston Celtics

28

SG

22

5-Jun

185

Georgia State

1148640

Jonas Jerebko

Boston Celtics

8

PF

29

10-Jun

231

5000000

Amir Johnson

Boston Celtics

90

PF

29

9-Jun

240

12000000

Jordan Mickey

Boston Celtics

55

PF

21

8-Jun

235

LSU

1170960

Kelly Olynyk

Boston Celtics

41

C

25

Jul-00

238

Gonzaga

2165160

Terry Rozier

Boston Celtics

12

PG

22

2-Jun

190

Louisville

1824360

This section includes the following examples:

Counting Rows in a Table

This example shows how to count rows in a table:

t=> SELECT COUNT(*) FROM nba;
count
-----
457

Counting Distinct Values in a Table

This example shows how to count distinct values in a table:

The following structures generate the same result:

t=> SELECT COUNT(distinct "Age") FROM nba;
count
-----
22
t=> SELECT COUNT(*) FROM (SELECT "Age" FROM nba GROUP BY 1);
count
-----
22

Combining COUNT with Other Aggregates

This example shows how to combine the COUNT function with other aggregates:

t=> SELECT "Age", AVG("Salary") as "Average salary", COUNT(*) as "Number of players" FROM nba GROUP BY 1;
Age | Average salary | Number of players
----+----------------+------------------
 19 |        1930440 |                 2
 20 |        2725790 |                19
 21 |        2067379 |                19
 22 |        2357963 |                26
 23 |        2034746 |                41
 24 |        3785300 |                47
 25 |        3930867 |                45
 26 |        6866566 |                36
 27 |        6676741 |                41
 28 |        5110188 |                31
 29 |        6224177 |                28
 30 |        7061858 |                31
 31 |        8511396 |                22
 32 |        7716958 |                13
 33 |        3930739 |                14
 34 |        7606030 |                10
 35 |        3461739 |                 9
 36 |        2238119 |                10
 37 |       12777778 |                 4
 38 |        1840041 |                 4
 39 |        2517872 |                 2
 40 |        4666916 |                 3