Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

SUM (window function) ​

Compute the sum for an expression based on a window definition that contains an optional frame clause.

SUM ([ expression ]) OVER { window_name | ( [ window_definition ] ) }

For the full window_definition syntax, see Syntax for Window Functions.

For example:

premdb=# select name, city, capacity, sum(capacity) over(partition by city order by name) as sum_cap 
from team where city in('London','Birmingham','Liverpool') order by 2,1;
        name         |    city    | capacity | sum_cap 
----------------------+------------+----------+---------
 Aston Villa          | Birmingham |    42785 |   42785
 Birmingham City      | Birmingham |    30016 |   72801
 West Bromwich Albion | Birmingham |    27000 |   99801
 Everton              | Liverpool  |    40221 |   40221
 Liverpool            | Liverpool  |    44742 |   84963
 Arsenal              | London     |    60260 |   60260
 Charlton Athletic    | London     |    27111 |   87371
 Chelsea              | London     |    41663 |  129034
 Crystal Palace       | London     |    26255 |  155289
 Fulham               | London     |    25700 |  180989
 Queens Park Rangers  | London     |    18439 |  199428
 Tottenham Hotspur    | London     |    36284 |  235712
 West Ham United      | London     |    35016 |  270728
 Wimbledon            | London     |    26255 |  296983
(14 rows)

Parent topic:Window Functions