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

Name

Team

Number

Position

Age

Height

Weight

College

Salary

Avery Bradley

Boston Celtics

0.0

PG

25.0

6-2

180.0

Texas

7730337.0

Jae Crowder

Boston Celtics

99.0

SF

25.0

6-6

235.0

Marquette

6796117.0

John Holland

Boston Celtics

30.0

SG

27.0

6-5

205.0

Boston University

R.J. Hunter

Boston Celtics

28.0

SG

22.0

6-5

185.0

Georgia State

1148640.0

Jonas Jerebko

Boston Celtics

8.0

PF

29.0

6-10

231.0

5000000.0

Amir Johnson

Boston Celtics

90.0

PF

29.0

6-9

240.0

12000000.0

Jordan Mickey

Boston Celtics

55.0

PF

21.0

6-8

235.0

LSU

1170960.0

Kelly Olynyk

Boston Celtics

41.0

C

25.0

7-0

238.0

Gonzaga

2165160.0

Terry Rozier

Boston Celtics

12.0

PG

22.0

6-2

190.0

Louisville

1824360.0

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