Product Documentation

Appearance

COUNT (window function) ​

Compute the count over an expression, based on a window definition and an optional frame clause. Three variations are supported:

  • COUNT(*) (all rows)
  • COUNT([ALL] expression) (all non-NULL values, including duplicates)
  • COUNT(DISTINCT expression) (all distinct non-NULL values)
COUNT ( * | [ DISTINCT | [ ALL ] ] expression ]) OVER { window_name | ( [ window_definition ] ) }

ALL is an optional keyword; ALL is implied when DISTINCT is not used. For the full window_definition syntax, see Syntax for Window Functions.

Note: COUNT(DISTINCT) window functions cannot have an ORDER BY clause in the window definition or a non-default window frame.

For example:

premdb=# select name, city, capacity, count(capacity) 
over(partition by city order by name) as count_cap 
from team where city in('London','Birmingham','Liverpool') order by 2,1;
        name         |    city    | capacity | count_cap 
----------------------+------------+----------+-----------
 Aston Villa          | Birmingham |    42785 |         1
 Birmingham City      | Birmingham |    30016 |         2
 West Bromwich Albion | Birmingham |    27000 |         3
 Everton              | Liverpool  |    40221 |         1
 Liverpool            | Liverpool  |    44742 |         2
 Arsenal              | London     |    60260 |         1
 Charlton Athletic    | London     |    27111 |         2
 Chelsea              | London     |    41663 |         3
 Crystal Palace       | London     |    26255 |         4
 Fulham               | London     |    25700 |         5
 Queens Park Rangers  | London     |    18439 |         6
 Tottenham Hotspur    | London     |    36284 |         7
 West Ham United      | London     |    35016 |         8
 Wimbledon            | London     |    26255 |         9
(14 rows)

Add an explicit window frame to this example:

premdb=# select name, city, capacity, count(capacity) 
over(partition by city order by name rows between 2 preceding and 2 following) as count_cap 
from team where city in('London','Birmingham','Liverpool') order by 2,1;
        name         |    city    | capacity | count_cap 
----------------------+------------+----------+-----------
 Aston Villa          | Birmingham |    42785 |         3
 Birmingham City      | Birmingham |    30016 |         3
 West Bromwich Albion | Birmingham |    27000 |         3
 Everton              | Liverpool  |    40221 |         2
 Liverpool            | Liverpool  |    44742 |         2
 Arsenal              | London     |    60260 |         3
 Charlton Athletic    | London     |    27111 |         4
 Chelsea              | London     |    41663 |         5
 Crystal Palace       | London     |    26255 |         5
 Fulham               | London     |    25700 |         5
 Queens Park Rangers  | London     |    18439 |         5
 Tottenham Hotspur    | London     |    36284 |         5
 West Ham United      | London     |    35016 |         4
 Wimbledon            | London     |    26255 |         3
(14 rows)

The following example uses COUNT(DISTINCT expression). The query counts the number of distinct full-time scores that start with 7 for rows partitioned by half-time scores.

premdb=# select htscore, ftscore, count(distinct ftscore) over(partition by htscore) from match where ftscore like '7%' and htscore not in('-','') order by 1;
 htscore | ftscore | count 
---------+---------+-------
 1-1     | 7-3     |     1
 2-0     | 7-0     |     1
 2-0     | 7-1     |     2
 2-1     | 7-1     |     1
 2-1     | 7-4     |     2
 3-0     | 7-0     |     2
 3-0     | 7-0     |     2
 3-0     | 7-1     |     4
 3-0     | 7-1     |     4
 3-1     | 7-1     |     4
 3-1     | 7-1     |     4
 3-1     | 7-1     |     4
 3-1     | 7-1     |     4
 4-0     | 7-0     |     3
 4-0     | 7-0     |     3
 4-0     | 7-0     |     3
 4-0     | 7-2     |     4
 4-2     | 7-2     |     1
 5-1     | 7-1     |     1
 5-1     | 7-2     |     2
(20 rows)

Parent topic:Window Functions