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 |
---|---|
|
Any value expression |
|
Specifies that |
|
Specifies that the operation should operate only on unique values |
Returns¶
The
COUNT
function returnsBIGINT
.
Notes¶
The following notes apply to the COUNT
function:
When all rows contain
NULL
values, the function returnsNULL
.COUNT(*)
returns the number of items in a group, including duplicates andNULL
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" text,
"Team" text,
"Number" tinyint,
"Position" text,
"Age" tinyint,
"Height" text,
"Weight" real,
"College" text,
"Salary" float
);
The following table is a preview of the source nba.csv
table shown below:
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