SQL Window Functions

A window function allows for computation across several rows of a result set, similar to an aggregate function. However, rather than returning a single computed result across all of the rows in the result set, a window function returns each row that it operates on along with a value that the window function computes and associates with that row. The result set of a window function is not a grouped or summarized set of rows, as is the case with aggregate functions. Window functions can also be referred to as analytic functions.

A SQL SELECT statement column_list can contain multiple window functions. If you specify a window function then it must be an item in the SELECT column_list or ORDER BY clause. It cannot be used as an expression in a WHERE clause. It also cannot be used in a GROUP BY or HAVING clause. This is because NuoDB first evaluates the expressions in these clauses and then for each row, it executes the window function. Window functions are supported in derived tables and SQL sub-SELECT statements. These sub-SELECT statements can be used as an item in the SELECT statement column_list or in the WHERE clause.

Syntax

WINDOW_FUNCTION( arguments ) OVER ( [ partition_clause ] [ order_by_clause ] )

arguments

arguments passed to the window function. Not all window functions accept arguments. See specific functions below for details on window functions that allow arguments.

OVER

A window function contains an OVER clause. This is what differentiates it from other functions. This OVER clause will define how all of the rows being returned by the SQL SELECT statement will be grouped and sorted for calculating the returned value for each row.

The following parameters are contained in the OVER clause:

partion_clause

The partition_clause format is: PARTITION BY column_name column_name , …​ ]

The partition_clause is optional.

The partition_clause defines how the SELECT result set is to be grouped for computing the values returned by the window function. In each partition group, every row has the same value for all of the columns defined by the partition_clause. This group of rows is considered the window of rows. The window function returns a value for each row in the partition group, or window, based on some computed value across all rows in the partition group. See each individual window function below for a definition of the computed value for that window function.

If no partition_clause is provided, the window function will return a value for each row based on some computed value across the entire SELECT result set.

order_by_clause

The order_by_clause format is: ORDER BY column_name [ ASC | DESC ] column_name [ ASC | DESC ] , …​ ]

The order_by_clause is optional for all window functions except the ROW_NUMBER window function.

The order_by_clause defines the order in which the window function computes and returns the value for each row defined in the partition group, or window. If you specify a partition clause, then for each partition group, rows are ordered and assigned computed values based on this order_by_clause.

The order for each column can be specified in either ascending, the default, or descending order.

The order_by_clause is not the same as the SQL SELECT statement ORDER BY clause. Window functions are executed at the end of query execution, just before the SQL SELECT statement ORDER BY clause is evaluated. If there is no SQL SELECT statement ORDER BY clause, then the output rows are ordered by the columns specified by the window function order_by_clause, within the groups defined by the partition_clause, if one is provided. See the last two examples for the AVG() window function. This example uses an ORDER BY clause to change the ordering of output rows after the window function is executed.

See each window function below for syntax and how they each compute values.

ROW_NUMBER()
ROW_NUMBER() OVER ( [ partition_clause ] order_by_clause )

The ROW_NUMBER() window function will return a unique sequential number for each row, starting with 1 and incrementing by 1. This number is assigned to each row in the order defined by the order_by_clause. This order_by_clause is required since the value returned for each row is dependent on this ordering.

If a partition_clause is provided, the function will assign 1 to the first row returned in each partition group, 2 to the second row in each partition group, and so on. This value is reset back to 1 and incremented by 1 and assigned to rows in each partition group.

The following example assigns ROW_NUMBER to players based on their number of goals, highest to lowest. This ranking is partitioned by team. ROW_NUMBER always returns a unique value for every row in the partition. Therefore, players on the same team that have the same number of goals will be assigned a ROW_NUMBER value incrementally.

In this example, since there is no ORDER BY clause provided for the SQL SELECT statement, the output rows are sorted based on the window function order_by_clause, namely the player_goal column value in descending order, within each partition group, defined by team_name. The team_name column, added to the order_by_clause, causes the output rows to be ordered by team_name in ascending order. If the team_name is eliminated from the order_by_clause, then the output rows would have been ordered by team_name as specified by the partition_clause, but ordered in descending order, based on the descending order specified in the order_by_clause.
SELECT PLAYER_NAME, TEAM_NAME, POSITION, STINT, GOALS, ASSISTS
     , ROW_NUMBER() OVER (PARTITION BY TEAM_NAME ORDER BY TEAM_NAME ASC, GOALS DESC) AS PLAYER_GOAL
FROM ( SELECT p.firstname||' '|| p.lastname AS PLAYER_NAME
            , t.name as TEAM_NAME, s.position, s.stint
            , SUM(s.goals) AS GOALS, SUM(s.assists) AS ASSISTS
         FROM players p, scoring s, teams t
        WHERE p.playerid= s.playerid
          AND s.teamid = t.teamid and s.year = t.year
          AND s.year = 1976
        GROUP BY p.firstname||' '|| p.lastname, t.name, s.stint, s.position);

     PLAYER_NAME              TEAM_NAME         POSITION  STINT  GOALS  ASSISTS  PLAYER_GOAL
 -------------------- ------------------------- --------- ------ ------ -------- ------------

 Willi Plett          Atlanta Flames                R       1      33      23          1
 Eric Vail            Atlanta Flames                L       1      32      39          2
 Tom Lysiak           Atlanta Flames                C       1      30      51          3
 Curt Bennett         Atlanta Flames                L       1      22      25          4
 Ken Houston          Atlanta Flames                R       1      20      24          5
 Bill Clement         Atlanta Flames                C       1      17      26          6
 Guy Chouinard        Atlanta Flames                C       1      17      33          7
 Rey Comeau           Atlanta Flames                C       1      15      18          8
 Bobby Simpson        Atlanta Flames                L       1      13      10          9
 Richard Mulhern      Atlanta Flames                D       1      12      32         10
 Tim Ecclestone       Atlanta Flames                L       1       9      18         11
 Hilliard Graves      Atlanta Flames                R       1       8       5         12
 John Gould           Atlanta Flames                R       2       8      15         13
 Randy Manery         Atlanta Flames                D       1       5      24         14
 Dave Shand           Atlanta Flames                D       1       5      11         15
 Ed Kea               Atlanta Flames                D       1       4      21         16
 Bill Flett           Atlanta Flames                R       1       4       4         17
 Larry Romanchych     Atlanta Flames                R       1       4       5         18
 Larry Carriere       Atlanta Flames                D       1       2       3         19
 Pat Ribble           Atlanta Flames                D       1       2       2         20
 Pat Quinn            Atlanta Flames                D       1       1      12         21
 Barry Gibbs          Atlanta Flames                D       1       1      16         22
 Phil Myre            Atlanta Flames                G       1       0       2         23
 Dan Bouchard         Atlanta Flames                G       1       0       1         24
 Rick Bowness         Atlanta Flames                R       1       0       4         25
 Mark Napier          Birmingham Bulls              R       1      60      36          1
 Vaclav Nedomansky    Birmingham Bulls              R       1      36      33          2
 Tim Sheehy           Birmingham Bulls              R       2      26      21          3
 Peter Marrin         Birmingham Bulls              C       1      23      37          4
 Paul Henderson       Birmingham Bulls              L       1      23      25          5
 Jeff Jacques         Birmingham Bulls              R       1      21      27          6
 Lou Nistico          Birmingham Bulls              L       1      20      36          7
 John Stewart         Birmingham Bulls              C       1      17      24          8
 Dave Gorman          Birmingham Bulls              R       2       9      13          9
 Richard Farda        Birmingham Bulls              C       1       9      26         10
 Gavin Kirk           Birmingham Bulls              C       1       9      18         11
 Tom Simpson          Birmingham Bulls              R       1       7       6         12
 Dale Hoganson        Birmingham Bulls              D       1       7      48         13
 Jim Turkiewicz       Birmingham Bulls              D       1       6      25         14
 Gord Gallant         Birmingham Bulls              L       2       4      13         15
 Frank Mahovlich      Birmingham Bulls              L       1       3      20         16
 Gilles Bilodeau      Birmingham Bulls              L       1       2       6         17
 Jean-Guy Lagace      Birmingham Bulls              D       1       2      25         18
 Jean-Luc Phaneuf     Birmingham Bulls              R       1       2       7         19
 Wayne Carleton       Birmingham Bulls              C       1       1       0         20
 Pat Westrum          Birmingham Bulls              D       2       1      11         21
 Terry Ball           Birmingham Bulls              D       1       1       6         22
 Wayne Wood           Birmingham Bulls              G       1       0       0         23
 John Stewart         Birmingham Bulls              L       2       0       0         24
 Rich Hart            Birmingham Bulls              D       1       0       0         25
 Jerry Rollins        Birmingham Bulls              D       1       0       0         26
 Keith Kokkola        Birmingham Bulls              D       1       0       0         27
 Peter Folco          Birmingham Bulls              D       1       0       0         28
 Rick Cunningham      Birmingham Bulls              D       1       0       8         29
 Jim Marsh            Birmingham Bulls              D       1       0       0         30
 Dave Syvret          Birmingham Bulls              D       1       0       0         31
 Buzz Schneider       Birmingham Bulls              L       1       0       0         32
 Ray McKay            Birmingham Bulls              D       2       0       1         33
 John Garrett         Birmingham Bulls              G       1       0       3         34
 Paul Heaver          Birmingham Bulls              D       1       0       0         35
 Peter McNab          Boston Bruins                 C       1      38      48          1
 Jean Ratelle         Boston Bruins                 C       1      33      61          2
 Gregg Sheppard       Boston Bruins                 C       1      31      36          3
 Don Marcotte         Boston Bruins                 L       1      27      18          4
 Bobby Schmautz       Boston Bruins                 R       1      23      29          5
 John Bucyk           Boston Bruins                 L       1      20      23          6
 Rick Middleton       Boston Bruins                 R       1      20      22          7
 Stan Jonathan        Boston Bruins                 L       1      17      13          8
 Wayne Cashman        Boston Bruins                 L       1      15      37          9
 Terry O'Reilly       Boston Bruins                 R       1      14      41         10
 Brad Park            Boston Bruins                 D       1      12      55         11
 Matti Hagman         Boston Bruins                 C       1      11      17         12
 Earl Anderson        Boston Bruins                 R       1      10      11         13
 Dave Forbes          Boston Bruins                 L       1       9      11         14
 Hank Nowak           Boston Bruins                 L       1       7       5         15
 Mike Milbury         Boston Bruins                 D       1       6      18         16
 Rick Smith           Boston Bruins                 D       2       6      16         17
 John Wensink         Boston Bruins                 L       1       4       6         18
 Gary Doak            Boston Bruins                 D       1       3      13         19
 Doug Halward         Boston Bruins                 D       1       2       2         20
 Ray Maluta           Boston Bruins                 D       1       2       3         21
 Dallas Smith         Boston Bruins                 D       1       2      20         22
 Darryl Edestrand     Boston Bruins                 D       1       0       3         23
 Gerry Cheevers       Boston Bruins                 G       1       0       4         24
 Clayton Pachal       Boston Bruins                 L       1       0       0         25
 Joe Zanussi          Boston Bruins                 D       1       0       1         26
 Gilles Gilbert       Boston Bruins                 G       1       0       1         27
 Al Sims              Boston Bruins                 D       1       0       0         28
 Jim Pettie           Boston Bruins                 G       1       0       0         29
...

In the previous example, there are several players with the same number of goals. Some examples have been highlighted. The ROW_NUMBER() window function can compute a different value for each of these duplicate rows every time it executes, depending on the order in which it sorts the duplicate rows. Multiple order_by_clause columns can be provided to further defined the ordering of these duplicate rows.

In the following example, the assists column s is added to the order_by_clause. This re-orders these duplicate rows, where players have the same number of goals. The ROW_NUMBER() window function assigns values now ordered by number of goals and number of assists within goals.

SELECT PLAYER_NAME, TEAM_NAME, POSITION, STINT, GOALS, ASSISTS
     , ROW_NUMBER() OVER (PARTITION BY TEAM_NAME ORDER BY TEAM_NAME ASC
                                                     , GOALS DESC) AS PLAYER_GOAL
     , ROW_NUMBER() OVER (PARTITION BY TEAM_NAME ORDER BY TEAM_NAME ASC
                                                     , GOALS DESC, ASSISTS DESC) AS PLAYER_GOAL_ASSIST
FROM ( SELECT p.firstname||' '|| p.lastname AS PLAYER_NAME
            , t.name as TEAM_NAME, s.position, s.stint
            , SUM(s.goals) AS GOALS, SUM(s.assists) AS ASSISTS
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976
GROUP BY p.firstname||' '|| p.lastname, t.name, s.stint, s.position);

     PLAYER_NAME              TEAM_NAME         POSITION  STINT  GOALS  ASSISTS  PLAYER_GOAL  PLAYER_GOAL_ASSIST
 -------------------- ------------------------- --------- ------ ------ -------- ------------ -------------------

 Willi Plett          Atlanta Flames                R       1      33      23          1               1
 Eric Vail            Atlanta Flames                L       1      32      39          2               2
 Tom Lysiak           Atlanta Flames                C       1      30      51          3               3
 Curt Bennett         Atlanta Flames                L       1      22      25          4               4
 Ken Houston          Atlanta Flames                R       1      20      24          5               5
 Guy Chouinard        Atlanta Flames                C       1      17      33          7               6
 Bill Clement         Atlanta Flames                C       1      17      26          6               7
 Rey Comeau           Atlanta Flames                C       1      15      18          8               8
 Bobby Simpson        Atlanta Flames                L       1      13      10          9               9
 Richard Mulhern      Atlanta Flames                D       1      12      32         10              10
 Tim Ecclestone       Atlanta Flames                L       1       9      18         11              11
 John Gould           Atlanta Flames                R       2       8      15         13              12
 Hilliard Graves      Atlanta Flames                R       1       8       5         12              13
 Randy Manery         Atlanta Flames                D       1       5      24         14              14
 Dave Shand           Atlanta Flames                D       1       5      11         15              15
 Ed Kea               Atlanta Flames                D       1       4      21         16              16
 Larry Romanchych     Atlanta Flames                R       1       4       5         18              17
 Bill Flett           Atlanta Flames                R       1       4       4         17              18
 Larry Carriere       Atlanta Flames                D       1       2       3         19              19
 Pat Ribble           Atlanta Flames                D       1       2       2         20              20
 Barry Gibbs          Atlanta Flames                D       1       1      16         22              21
 Pat Quinn            Atlanta Flames                D       1       1      12         21              22
 Rick Bowness         Atlanta Flames                R       1       0       4         25              23
 Phil Myre            Atlanta Flames                G       1       0       2         23              24
 Dan Bouchard         Atlanta Flames                G       1       0       1         24              25
...

The following example returns the three highest scoring players in the NHL for the year 1976.

SELECT * FROM
(SELECT p.playerid, p.firstname||' '||p.lastname AS PLAYER_NAME
            , t.name as TEAM_NAME, s.position,s.goals, s.assists
            , ROW_NUMBER() OVER (ORDER BY s.goals DESC) AS PLAYER_NUM
     FROM players p, scoring s, teams t
     WHERE p.playerid= s.playerid
    AND s.teamid = t.teamid and s.year = t.year
     AND s.year = 1976)
 WHERE player_num < 5;

  PLAYERID   PLAYER_NAME       TEAM_NAME      POSITION  GOALS  ASSISTS  PLAYER_NUM
 --------- -------------- ------------------ --------- ------ -------- -----------

 hedbean01 Anders Hedberg Winnipeg Jets          R       70      61         1
 cloutre01 Real Cloutier  Quebec Nordiques       R       66      75         2
 napiema01 Mark Napier    Birmingham Bulls       R       60      36         3
 shuttst01 Steve Shutt    Montreal Canadiens     L       60      45         4

In the previous example, there are two players with the same number of goals, both having 60 goals. Because the ROW_NUMBER() window function returns a unique value for each row, these two players are assigned unique values based on some ordering of rows that may not be consistent from one execution to the next. Therefore, there is no guarantee that the ROW_NUMBER() window function will assign these same value these last two row each time the SQL statement executes. For the window function to return consistent values, one or more columns should be used in the order_by_clause that allows the window function to order the results consistently. In the following example, adding the assists column to the order_by_clause changes the results, as follows:

SELECT * FROM
(SELECT p.playerid, p.firstname||' '||p.lastname AS PLAYER_NAME
            , t.name as TEAM_NAME, s.position,s.goals, s.assists
            , ROW_NUMBER() OVER (ORDER BY s.goals DESC, s.assists DESC) AS PLAYER_NUM
     FROM players p, scoring s, teams t
     WHERE p.playerid= s.playerid
    AND s.teamid = t.teamid and s.year = t.year
     AND s.year = 1976)
 WHERE player_num < 5;

  PLAYERID   PLAYER_NAME       TEAM_NAME      POSITION  GOALS  ASSISTS  PLAYER_NUM
 --------- -------------- ------------------ --------- ------ -------- -----------

 hedbean01 Anders Hedberg Winnipeg Jets          R       70      61         1
 cloutre01 Real Cloutier  Quebec Nordiques       R       66      75         2
 shuttst01 Steve Shutt    Montreal Canadiens     L       60      45         3
 napiema01 Mark Napier    Birmingham Bulls       R       60      36         4
COUNT()
COUNT( expression ) OVER ( [ partition_clause ] order_by_clause )

The COUNT() window function computes the number of rows in the window. The expression can be defined as an asterick (*), which counts all rows, or a column_name, which counts all rows that have a non-NULL value as specified by the expression. The expression cannot include DISTINCT.

The function uses the order_by_clause to determine the rows to include in computing the COUNT() value. This COUNT() value is cumulative. Therefore, the COUNT() value for the first row is computed using the first row. The COUNT() value for the second row is computed to include the first and second row, and so on. If the expression is defined as a column_name, and that row, or any row previous, contains a NULL value, that row is not included in the computed value,

If the order_by_clause is not provided, this count expression value is computed across all rows in the window and the result is assigned to each row in the window. In other words, the same value is assigned to each row and that value is the total rows in the window.

If a partition_clause is provided, this COUNT() value is reset for the first row returned in each partition group based on the partition_clause.

In the following example, we can use the COUNT aggregate function to return the number of players on each team in the year 1976 by using the GROUP BY clause on team.

SELECT t.name as tEAM_NAM, count(*)
  FROM players p, scoring s, teams t
 WHERE p.playerid  = s.playerid
   AND s.teamid = t.teamid and s.year = t.year
   AND t.year = 1976
GROUP BY t.name ORDER BY t.name;

         TEAM_NAM          COUNT
 ------------------------- ------

 Atlanta Flames              25
 Birmingham Bulls            35
 Boston Bruins               29
 Buffalo Sabres              26
 Calgary Cowboys             41
 Chicago Black Hawks         31
 Cincinnati Stingers         26
 Cleveland Barons            35
 Colorado Rockies            32
 Detroit Red Wings           36
 Edmonton Oilers             42
 Houston Aeros               26
 Indianapolis Racers         30
 Los Angeles Kings           27
 Minnesota Fighting Saints   24
 Minnesota North Stars       27
 Montreal Canadiens          25
 New England Whalers         33
 New York Islanders          24
 New York Rangers            30
 Philadelphia Flyers         32
 Phoenix Roadrunners         26
 Pittsburgh Penguins         28
 Quebec Nordiques            25
 San Diego Mariners          27
 St. Louis Blues             31
 Toronto Maple Leafs         28
 Vancouver Canucks           31
 Washington Capitals         35
 Winnipeg Jets               28

This same result can be returned by using the COUNT() window function, as follows:

SELECT distinct * FROM
  ( SELECT t.name as TEAM_NAME
         , count(p.playerid) over (partition by t.teamid)
      FROM players p, scoring s, teams t
     WHERE p.playerid  = s.playerid
       AND s.teamid = t.teamid and s.year = t.year
       AND s.year = 1976);

But what if, in addition to selecting this aggregate count by team, we wanted to select the names of the players for each team. The COUNT() window function allows us to do this. Window functions provide a way to select both the group by column(s) as well as each individual row in the group.

SELECT p.firstname||' '||p.lastname AS PLAYER_NAME
 ,t.name as TEAM_NAME
 ,count(p.playerid) over (partition by t.teamid)
FROM players p, scoring s, teams t
     WHERE p.playerid  = s.playerid
     AND s.teamid = t.teamid and s.year = t.year
     AND s.year = 1976
ORDER BY t.name, PLAYER_NAME;

     PLAYER_NAME              TEAM_NAME         COUNT
 -------------------- ------------------------- ------

 Barry Gibbs          Atlanta Flames              25
 Bill Clement         Atlanta Flames              25
 Bill Flett           Atlanta Flames              25
 Bobby Simpson        Atlanta Flames              25
 Curt Bennett         Atlanta Flames              25
 Dan Bouchard         Atlanta Flames              25
 Dave Shand           Atlanta Flames              25
 Ed Kea               Atlanta Flames              25
 Eric Vail            Atlanta Flames              25
 Guy Chouinard        Atlanta Flames              25
 Hilliard Graves      Atlanta Flames              25
 John Gould           Atlanta Flames              25
 Ken Houston          Atlanta Flames              25
 Larry Carriere       Atlanta Flames              25
 Larry Romanchych     Atlanta Flames              25
 Pat Quinn            Atlanta Flames              25
 Pat Ribble           Atlanta Flames              25
 Phil Myre            Atlanta Flames              25
 Randy Manery         Atlanta Flames              25
 Rey Comeau           Atlanta Flames              25
 Richard Mulhern      Atlanta Flames              25
 Rick Bowness         Atlanta Flames              25
 Tim Ecclestone       Atlanta Flames              25
 Tom Lysiak           Atlanta Flames              25
 Willi Plett          Atlanta Flames              25
 Buzz Schneider       Birmingham Bulls            35
 Dale Hoganson        Birmingham Bulls            35
 Dave Gorman          Birmingham Bulls            35
 Dave Syvret          Birmingham Bulls            35
 Frank Mahovlich      Birmingham Bulls            35
 Gavin Kirk           Birmingham Bulls            35
 Gilles Bilodeau      Birmingham Bulls            35
 Gord Gallant         Birmingham Bulls            35
 Jean-Guy Lagace      Birmingham Bulls            35
 Jean-Luc Phaneuf     Birmingham Bulls            35
 Jeff Jacques         Birmingham Bulls            35
 Jerry Rollins        Birmingham Bulls            35
 Jim Marsh            Birmingham Bulls            35
 Jim Turkiewicz       Birmingham Bulls            35
 John Garrett         Birmingham Bulls            35
 John Stewart         Birmingham Bulls            35
 John Stewart         Birmingham Bulls            35
 Keith Kokkola        Birmingham Bulls            35
 Lou Nistico          Birmingham Bulls            35
 Mark Napier          Birmingham Bulls            35
 Pat Westrum          Birmingham Bulls            35
 Paul Heaver          Birmingham Bulls            35
 Paul Henderson       Birmingham Bulls            35
 Peter Folco          Birmingham Bulls            35
 Peter Marrin         Birmingham Bulls            35
 Ray McKay            Birmingham Bulls            35
 Rich Hart            Birmingham Bulls            35
 Richard Farda        Birmingham Bulls            35
 Rick Cunningham      Birmingham Bulls            35
 Terry Ball           Birmingham Bulls            35
 Tim Sheehy           Birmingham Bulls            35
 Tom Simpson          Birmingham Bulls            35
 Vaclav Nedomansky    Birmingham Bulls            35
 Wayne Carleton       Birmingham Bulls            35
 Wayne Wood           Birmingham Bulls            35
 Al Sims              Boston Bruins               29
 Bobby Schmautz       Boston Bruins               29
 Brad Park            Boston Bruins               29
 Clayton Pachal       Boston Bruins               29
 Dallas Smith         Boston Bruins               29
 Darryl Edestrand     Boston Bruins               29
 Dave Forbes          Boston Bruins               29
 Don Marcotte         Boston Bruins               29
 Doug Halward         Boston Bruins               29
 Earl Anderson        Boston Bruins               29
 Gary Doak            Boston Bruins               29
 Gerry Cheevers       Boston Bruins               29
 Gilles Gilbert       Boston Bruins               29
 Gregg Sheppard       Boston Bruins               29
 Hank Nowak           Boston Bruins               29
 Jean Ratelle         Boston Bruins               29
 Jim Pettie           Boston Bruins               29
 Joe Zanussi          Boston Bruins               29
 John Bucyk           Boston Bruins               29
 John Wensink         Boston Bruins               29
 Matti Hagman         Boston Bruins               29
 Mike Milbury         Boston Bruins               29
 Peter McNab          Boston Bruins               29
 Ray Maluta           Boston Bruins               29
 Rick Middleton       Boston Bruins               29
 Rick Smith           Boston Bruins               29
 Stan Jonathan        Boston Bruins               29
 Terry O'Reilly       Boston Bruins               29
 Wayne Cashman        Boston Bruins               29
...
SUM()
SUM( expression ) OVER ( [ partition_clause ] order_by_clause )

The SUM() window function computes the sum value for the item identified by the expression argument. This expression can be defined as a table column_name, an arithmetic expression of columns and/or variables, etc.

The function uses the order_by_clause to sort the rows. It then computes the sum for the column(s) identified by the expression. For the first row in the window, the function uses only that row to calculate the sum. It then assigns that value to the first row. For the second row in the window, the function uses the first and second rows to calculate the sum and then the function assigns this value to the second row. For the third row in the window, the function uses the first three rows to calculate the sum, and so on.

If a partition_clause is provided, this total expression value computation is reset for the first row returned in each partition group, or window, based on the partition_clause.

The following example returns, for all teams in the NHL, each player’s weight and height. It uses the SUM() window function to return the total weight and height associated with each row in the window. The total expression value is computed for each row in the partition by using the current row plus all previous rows in the partition and sorted based on the order_by_clause

SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
     , p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
     , SUM(p.weight) OVER (PARTITION BY t.name order by p.firstname) AS TOTAL_WEIGHT
     , SUM(p.height) OVER (PARTITION BY t.name ORDER BY p.firstname) AS TOTAL_HEIGHT
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;
         TEAM_NAME         YEAR      PLAYER_NAME      PLAYER_WEIGHT  PLAYER_HEIGHT  TOTAL_WEIGHT  TOTAL_HEIGHT
 ------------------------- ----- -------------------- -------------- -------------- ------------- -------------

 Atlanta Flames            1976  Barry Gibbs               195             71            195            71
 Atlanta Flames            1976  Bill Clement              194             73            594           217
 Atlanta Flames            1976  Bill Flett                205             73            594           217
 Atlanta Flames            1976  Bobby Simpson             190             72            784           289
 Atlanta Flames            1976  Curt Bennett              195             75            979           364
 Atlanta Flames            1976  Dan Bouchard              190             72           1169           436
 Atlanta Flames            1976  Dave Shand                200             74           1369           510
 Atlanta Flames            1976  Ed Kea                    200             75           1569           585
 Atlanta Flames            1976  Eric Vail                 220             73           1789           658
 Atlanta Flames            1976  Guy Chouinard             182             71           1971           729
 Atlanta Flames            1976  Hilliard Graves           175             71           2146           800
 Atlanta Flames            1976  John Gould                197             71           2343           871
 Atlanta Flames            1976  Ken Houston               210             74           2553           945
 Atlanta Flames            1976  Larry Romanchych          180             73           2923          1091
 Atlanta Flames            1976  Larry Carriere            190             73           2923          1091
 Atlanta Flames            1976  Pat Quinn                 205             75           3338          1242
 Atlanta Flames            1976  Pat Ribble                210             76           3338          1242
 Atlanta Flames            1976  Phil Myre                 185             73           3523          1315
 Atlanta Flames            1976  Randy Manery              185             72           3708          1387
 Atlanta Flames            1976  Rey Comeau                190             68           3898          1455
 Atlanta Flames            1976  Richard Mulhern           188             73           4086          1528
 Atlanta Flames            1976  Rick Bowness              185             73           4271          1601
 Atlanta Flames            1976  Tim Ecclestone            195             70           4466          1671
 Atlanta Flames            1976  Tom Lysiak                185             73           4651          1744
 Atlanta Flames            1976  Willi Plett               205             75           4856          1819
 Birmingham Bulls          1976  Buzz Schneider            175             71            175            71
 Birmingham Bulls          1976  Dale Hoganson             190             70            365           141
 Birmingham Bulls          1976  Dave Syvret               190             72            740           284
 Birmingham Bulls          1976  Dave Gorman               185             71            740           284
 Birmingham Bulls          1976  Frank Mahovlich           205             72            945           356
 Birmingham Bulls          1976  Gavin Kirk                165             70           1110           426
 Birmingham Bulls          1976  Gilles Bilodeau           220             73           1330           499
 Birmingham Bulls          1976  Gord Gallant              175             71           1505           570
 Birmingham Bulls          1976  Jean-Guy Lagace           185             70           1690           640
 Birmingham Bulls          1976  Jean-Luc Phaneuf          165             68           1855           708
 Birmingham Bulls          1976  Jeff Jacques              180             71           2035           779
 Birmingham Bulls          1976  Jerry Rollins             195             74           2230           853
 Birmingham Bulls          1976  Jim Turkiewicz            185             70           2595           995
 Birmingham Bulls          1976  Jim Marsh                 180             72           2595           995
 Birmingham Bulls          1976  John Stewart              180             72           3130          1207
 Birmingham Bulls          1976  John Garrett              175             68           3130          1207
 Birmingham Bulls          1976  John Stewart              180             72           3130          1207
 Birmingham Bulls          1976  Keith Kokkola             210             75           3340          1282
 Birmingham Bulls          1976  Lou Nistico               170             67           3510          1349
 Birmingham Bulls          1976  Mark Napier               182             70           3692          1419
 Birmingham Bulls          1976  Pat Westrum               185             70           3877          1489
 Birmingham Bulls          1976  Paul Henderson            180             71           4252          1633
 Birmingham Bulls          1976  Paul Heaver               195             73           4252          1633
 Birmingham Bulls          1976  Peter Marrin              160             70           4597          1775
 Birmingham Bulls          1976  Peter Folco               185             72           4597          1775
 Birmingham Bulls          1976  Ray McKay                 183             76           4780          1851
 Birmingham Bulls          1976  Rich Hart                 195             72           4975          1923
 Birmingham Bulls          1976  Richard Farda             175             69           5150          1992
 Birmingham Bulls          1976  Rick Cunningham           180             71           5330          2063
 Birmingham Bulls          1976  Terry Ball                160             69           5490          2132
 Birmingham Bulls          1976  Tim Sheehy                185             73           5675          2205
 Birmingham Bulls          1976  Tom Simpson               190             69           5865          2274
 Birmingham Bulls          1976  Vaclav Nedomansky         205             74           6070          2348
 Birmingham Bulls          1976  Wayne Wood                190             73           6472          2496
 Birmingham Bulls          1976  Wayne Carleton            212             75           6472          2496
 Boston Bruins             1976  Al Sims                   182             72            182            72
 Boston Bruins             1976  Bobby Schmautz            172             69            354           141
 Boston Bruins             1976  Brad Park                 200             72            554           213
 Boston Bruins             1976  Clayton Pachal            185             70            739           283
 Boston Bruins             1976  Dallas Smith              180             71            919           354
 Boston Bruins             1976  Darryl Edestrand          180             71           1099           425
 Boston Bruins             1976  Dave Forbes               180             70           1279           495
 Boston Bruins             1976  Don Marcotte              183             71           1462           566
 Boston Bruins             1976  Doug Halward              200             73           1662           639
 Boston Bruins             1976  Earl Anderson             185             73           1847           712
 Boston Bruins             1976  Gary Doak                 175             71           2022           783
 Boston Bruins             1976  Gerry Cheevers            185             71           2207           854
 Boston Bruins             1976  Gilles Gilbert            175             73           2382           927
 Boston Bruins             1976  Gregg Sheppard            170             68           2552           995
 Boston Bruins             1976  Hank Nowak                195             73           2747          1068
 Boston Bruins             1976  Jean Ratelle              180             73           2927          1141
 Boston Bruins             1976  Jim Pettie                195             72           3122          1213
 Boston Bruins             1976  Joe Zanussi               180             70           3302          1283
 Boston Bruins             1976  John Wensink              200             72           3717          1427
 Boston Bruins             1976  John Bucyk                215             72           3717          1427
 Boston Bruins             1976  Matti Hagman              183             72           3900          1499
 Boston Bruins             1976  Mike Milbury              200             73           4100          1572
 Boston Bruins             1976  Peter McNab               210             75           4310          1647
 Boston Bruins             1976  Ray Maluta                173             68           4483          1715
 Boston Bruins             1976  Rick Smith                190             71           4843          1857
 Boston Bruins             1976  Rick Middleton            170             71           4843          1857
 Boston Bruins             1976  Stan Jonathan             175             68           5018          1925
 Boston Bruins             1976  Terry O'Reilly            200             73           5218          1998
 Boston Bruins             1976  Wayne Cashman             208             73           5426          2071
 ...

In the previous example, take a look at the 2nd and 3rd rows. Both have "Bill" as a FirstName. The SUM() window function will return the same cumulative value for each row that has the same order_by_clause column value within the window partition. If we change the order_by_clause to include multiple columns that will result in unique values, the SUM() window function will compute cumulative and not duplicate values for the same 2nd and 3rd rows.

SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
     , p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
     , SUM(p.weight) OVER (PARTITION BY t.name order by p.firstname,p.lastname) AS TOTAL_WEIGHT
     , SUM(p.height) OVER (PARTITION BY t.name ORDER BY p.firstname,p.lastname) AS TOTAL_HEIGHT
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;

         TEAM_NAME         YEAR      PLAYER_NAME      PLAYER_WEIGHT  PLAYER_HEIGHT  TOTAL_WEIGHT  TOTAL_HEIGHT
 ------------------------- ----- -------------------- -------------- -------------- ------------- -------------

 Atlanta Flames            1976  Barry Gibbs               195             71            195            71
 Atlanta Flames            1976  Bill Clement              194             73            389           144
 Atlanta Flames            1976  Bill Flett                205             73            594           217
 Atlanta Flames            1976  Bobby Simpson             190             72            784           289
 Atlanta Flames            1976  Curt Bennett              195             75            979           364
 Atlanta Flames            1976  Dan Bouchard              190             72           1169           436
 Atlanta Flames            1976  Dave Shand                200             74           1369           510
 Atlanta Flames            1976  Ed Kea                    200             75           1569           585
 Atlanta Flames            1976  Eric Vail                 220             73           1789           658
 Atlanta Flames            1976  Guy Chouinard             182             71           1971           729
 Atlanta Flames            1976  Hilliard Graves           175             71           2146           800
 Atlanta Flames            1976  John Gould                197             71           2343           871
 Atlanta Flames            1976  Ken Houston               210             74           2553           945
 Atlanta Flames            1976  Larry Carriere            190             73           2743          1018
 Atlanta Flames            1976  Larry Romanchych          180             73           2923          1091
 Atlanta Flames            1976  Pat Quinn                 205             75           3128          1166
 Atlanta Flames            1976  Pat Ribble                210             76           3338          1242
 Atlanta Flames            1976  Phil Myre                 185             73           3523          1315
 Atlanta Flames            1976  Randy Manery              185             72           3708          1387
 Atlanta Flames            1976  Rey Comeau                190             68           3898          1455
 Atlanta Flames            1976  Richard Mulhern           188             73           4086          1528
 Atlanta Flames            1976  Rick Bowness              185             73           4271          1601
 Atlanta Flames            1976  Tim Ecclestone            195             70           4466          1671
 Atlanta Flames            1976  Tom Lysiak                185             73           4651          1744
 Atlanta Flames            1976  Willi Plett               205             75           4856          1819
...

We can also remove the order_by_clause and change the report to show, for each team, in the year 1976, the total number of goals for that team. This can be done with the SUM() window function, partitioned by the team.

SELECT DISTINCT * FROM (
SELECT t.name AS TEAM_NAME
 ,SUM(s.goals) OVER (PARTITION BY s.teamid)
FROM scoring s, teams t
     WHERE  s.teamid = t.teamid AND s.year = t.year
     AND s.year = 1976);

         TEAM_NAME         SUM
 ------------------------- ----

 Atlanta Flames            264
 Birmingham Bulls          289
 Boston Bruins             312
 Buffalo Sabres            301
 Calgary Cowboys           252
 Chicago Black Hawks       240
 Cincinnati Stingers       354
 Cleveland Barons          240
 Colorado Rockies          226
 Detroit Red Wings         183
 Edmonton Oilers           243
 Houston Aeros             320
 Indianapolis Racers       276
 Los Angeles Kings         271
 Minnesota Fighting Saints 136
 Minnesota North Stars     240
 Montreal Canadiens        387
 New England Whalers       275
 New York Islanders        288
 New York Rangers          272
 Philadelphia Flyers       323
 Phoenix Roadrunners       281
 Pittsburgh Penguins       240
 Quebec Nordiques          353
 San Diego Mariners        284
 St. Louis Blues           239
 Toronto Maple Leafs       301
 Vancouver Canucks         235
 Washington Capitals       221
 Winnipeg Jets             366
AVG()
AVG( expression ) OVER ( [ partition_clause ] order_by_clause )

The AVG() window function computes the average of the expression argument. This expression can be defined as a table column name or an arithmetic expression of table column and/or variable.

The order_by_clause is optional. If it is provided, the function uses the order_by_clause to sort the rows before computing the average value of the expression for any given row in the window. This average expression value is assigned to each row of the partition group cumulatively. Therefore, the AVG() window function uses only the first row of the partition group to calculate the average expression value for that row. It uses the first two rows of the partition group to compute the average expression value returned for the second row. It uses the first three rows of the partition group to compute the average expression value returned for the third row, and so on.

If the order_by_clause is not provided, this average expression value is computed across all rows in the window and the result is assigned to each row in the window. In other words, the same value is assigned to each row.

If a partition_clause is provided, this average expression value computation is reset for the first row returned in each partition group, or window, based on the partition_clause.

The following example returns, for all teams in the NHL, each player’s weight and height. It uses the AVG() window function to return the average weight and height associated with each row in the window. The average expression value is computed for each row in the partition by using the current row plus all previous rows in the partition and sorted based on the order_by_clause.

The AVG() window function returns a value with the same precision and scale as the precision and scale of the expression.
SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
     , p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
     , AVG(p.weight) OVER (PARTITION BY t.name order by p.firstname,p.lastname) AS AVG_WEIGHT
     , AVG(p.height) OVER (PARTITION BY t.name ORDER BY p.firstname,p.lastname) AS AVG_HEIGHT
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;

        TEAM_NAME         YEAR      PLAYER_NAME      PLAYER_WEIGHT  PLAYER_HEIGHT  AVG_WEIGHT  AVG_HEIGHT
 ------------------------- ----- -------------------- -------------- -------------- ----------- -----------

 Atlanta Flames            1976  Barry Gibbs               195             71           195         71
 Atlanta Flames            1976  Bill Clement              194             73           194         72
 Atlanta Flames            1976  Bill Flett                205             73           198         72
 Atlanta Flames            1976  Bobby Simpson             190             72           196         72
 Atlanta Flames            1976  Curt Bennett              195             75           195         72
 Atlanta Flames            1976  Dan Bouchard              190             72           194         72
 Atlanta Flames            1976  Dave Shand                200             74           195         72
 Atlanta Flames            1976  Ed Kea                    200             75           196         73
 Atlanta Flames            1976  Eric Vail                 220             73           198         73
 Atlanta Flames            1976  Guy Chouinard             182             71           197         72
 Atlanta Flames            1976  Hilliard Graves           175             71           195         72
 Atlanta Flames            1976  John Gould                197             71           195         72
 Atlanta Flames            1976  Ken Houston               210             74           196         72
 Atlanta Flames            1976  Larry Carriere            190             73           195         72
 Atlanta Flames            1976  Larry Romanchych          180             73           194         72
 Atlanta Flames            1976  Pat Quinn                 205             75           195         72
 Atlanta Flames            1976  Pat Ribble                210             76           196         73
 Atlanta Flames            1976  Phil Myre                 185             73           195         73
 Atlanta Flames            1976  Randy Manery              185             72           195         73
 Atlanta Flames            1976  Rey Comeau                190             68           194         72
 Atlanta Flames            1976  Richard Mulhern           188             73           194         72
 Atlanta Flames            1976  Rick Bowness              185             73           194         72
 Atlanta Flames            1976  Tim Ecclestone            195             70           194         72
 Atlanta Flames            1976  Tom Lysiak                185             73           193         72
 Atlanta Flames            1976  Willi Plett               205             75           194         72
 Birmingham Bulls          1976  Buzz Schneider            175             71           175         71
 Birmingham Bulls          1976  Dale Hoganson             190             70           182         70
 Birmingham Bulls          1976  Dave Gorman               185             71           183         70
 Birmingham Bulls          1976  Dave Syvret               190             72           185         71
 Birmingham Bulls          1976  Frank Mahovlich           205             72           189         71
 Birmingham Bulls          1976  Gavin Kirk                165             70           185         71
 Birmingham Bulls          1976  Gilles Bilodeau           220             73           190         71
 Birmingham Bulls          1976  Gord Gallant              175             71           188         71
 Birmingham Bulls          1976  Jean-Guy Lagace           185             70           187         71
 Birmingham Bulls          1976  Jean-Luc Phaneuf          165             68           185         70
 Birmingham Bulls          1976  Jeff Jacques              180             71           185         70
 Birmingham Bulls          1976  Jerry Rollins             195             74           185         71
 Birmingham Bulls          1976  Jim Marsh                 180             72           185         71
 Birmingham Bulls          1976  Jim Turkiewicz            185             70           185         71
 Birmingham Bulls          1976  John Garrett              175             68           184         70
 Birmingham Bulls          1976  John Stewart              180             72           184         71
 Birmingham Bulls          1976  John Stewart              180             72           184         71
 Birmingham Bulls          1976  Keith Kokkola             210             75           185         71
 Birmingham Bulls          1976  Lou Nistico               170             67           184         71
 Birmingham Bulls          1976  Mark Napier               182             70           184         70
 Birmingham Bulls          1976  Pat Westrum               185             70           184         70
 Birmingham Bulls          1976  Paul Heaver               195             73           185         71
 Birmingham Bulls          1976  Paul Henderson            180             71           184         71
 Birmingham Bulls          1976  Peter Folco               185             72           184         71
 Birmingham Bulls          1976  Peter Marrin              160             70           183         71
 Birmingham Bulls          1976  Ray McKay                 183             76           183         71
 Birmingham Bulls          1976  Rich Hart                 195             72           184         71
 Birmingham Bulls          1976  Richard Farda             175             69           183         71
 Birmingham Bulls          1976  Rick Cunningham           180             71           183         71
 Birmingham Bulls          1976  Terry Ball                160             69           183         71
 Birmingham Bulls          1976  Tim Sheehy                185             73           183         71
 Birmingham Bulls          1976  Tom Simpson               190             69           183         71
 Birmingham Bulls          1976  Vaclav Nedomansky         205             74           183         71
 Birmingham Bulls          1976  Wayne Carleton            212             75           184         71
 Birmingham Bulls          1976  Wayne Wood                190             73           184         71
 Boston Bruins             1976  Al Sims                   182             72           182         72
 Boston Bruins             1976  Bobby Schmautz            172             69           177         70
 Boston Bruins             1976  Brad Park                 200             72           184         71
 Boston Bruins             1976  Clayton Pachal            185             70           184         70
 Boston Bruins             1976  Dallas Smith              180             71           183         70
 Boston Bruins             1976  Darryl Edestrand          180             71           183         70
 Boston Bruins             1976  Dave Forbes               180             70           182         70
 Boston Bruins             1976  Don Marcotte              183             71           182         70
 Boston Bruins             1976  Doug Halward              200             73           184         71
 Boston Bruins             1976  Earl Anderson             185             73           184         71
 Boston Bruins             1976  Gary Doak                 175             71           183         71
 Boston Bruins             1976  Gerry Cheevers            185             71           183         71
 Boston Bruins             1976  Gilles Gilbert            175             73           183         71
 Boston Bruins             1976  Gregg Sheppard            170             68           182         71
 Boston Bruins             1976  Hank Nowak                195             73           183         71
 Boston Bruins             1976  Jean Ratelle              180             73           182         71
 Boston Bruins             1976  Jim Pettie                195             72           183         71
 Boston Bruins             1976  Joe Zanussi               180             70           183         71
 Boston Bruins             1976  John Bucyk                215             72           185         71
 Boston Bruins             1976  John Wensink              200             72           185         71
 Boston Bruins             1976  Matti Hagman              183             72           185         71
 Boston Bruins             1976  Mike Milbury              200             73           186         71
 Boston Bruins             1976  Peter McNab               210             75           187         71
 Boston Bruins             1976  Ray Maluta                173             68           186         71
 Boston Bruins             1976  Rick Middleton            170             71           186         71
 Boston Bruins             1976  Rick Smith                190             71           186         71
 Boston Bruins             1976  Stan Jonathan             175             68           185         71
 Boston Bruins             1976  Terry O'Reilly            200             73           186         71
 Boston Bruins             1976  Wayne Cashman             208             73           187         71
...

If the order_by_clause is removed, the average weight for each team is calculated and the result is assigned to each row in the team window. In other words, the same average weight is assigned to each row in the team window. Using this average weight value, we can then calculate, for each player on the team, the difference between their weigh and the average team weight.

SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
     , p.weight as PLAYER_WEIGHT
     , AVG(p.weight) OVER (PARTITION BY t.name ) AS AVG_TEAM_WEIGHT
     , p.weight - AVG(p.weight) OVER (PARTITION BY t.name ) AS WEIGHT_DIFF
 FROM players p, scoring s, teams t
 WHERE p.playerid= s.playerid
 AND s.teamid = t.teamid and s.year = t.year
 AND s.year = 1976;
         TEAM_NAME         YEAR      PLAYER_NAME      PLAYER_WEIGHT  AVG_TEAM_WEIGHT  WEIGHT_DIFF
 ------------------------- ----- -------------------- -------------- ---------------- ------------

 Atlanta Flames            1976  Hilliard Graves           175             194            -19
 Atlanta Flames            1976  John Gould                197             194              3
 Atlanta Flames            1976  Pat Quinn                 205             194             11
 Atlanta Flames            1976  Bill Flett                205             194             11
 Atlanta Flames            1976  Bobby Simpson             190             194             -4
 Atlanta Flames            1976  Curt Bennett              195             194              1
 Atlanta Flames            1976  Randy Manery              185             194             -9
 Atlanta Flames            1976  Eric Vail                 220             194             26
 Atlanta Flames            1976  Richard Mulhern           188             194             -6
 Atlanta Flames            1976  Tim Ecclestone            195             194              1
 Atlanta Flames            1976  Tom Lysiak                185             194             -9
 Atlanta Flames            1976  Phil Myre                 185             194             -9
 Atlanta Flames            1976  Larry Carriere            190             194             -4
 Atlanta Flames            1976  Guy Chouinard             182             194            -12
 Atlanta Flames            1976  Rick Bowness              185             194             -9
 Atlanta Flames            1976  Ed Kea                    200             194              6
 Atlanta Flames            1976  Barry Gibbs               195             194              1
 Atlanta Flames            1976  Larry Romanchych          180             194            -14
 Atlanta Flames            1976  Bill Clement              194             194              0
 Atlanta Flames            1976  Dave Shand                200             194              6
 Atlanta Flames            1976  Pat Ribble                210             194             16
 Atlanta Flames            1976  Ken Houston               210             194             16
 Atlanta Flames            1976  Dan Bouchard              190             194             -4
 Atlanta Flames            1976  Rey Comeau                190             194             -4
 Atlanta Flames            1976  Willi Plett               205             194             11
 Birmingham Bulls          1976  Paul Henderson            180             184             -4
 Birmingham Bulls          1976  Jerry Rollins             195             184             11
 Birmingham Bulls          1976  Paul Heaver               195             184             11
 Birmingham Bulls          1976  Lou Nistico               170             184            -14
 Birmingham Bulls          1976  Peter Marrin              160             184            -24
 Birmingham Bulls          1976  Rich Hart                 195             184             11
 Birmingham Bulls          1976  Jim Marsh                 180             184             -4
 Birmingham Bulls          1976  Jeff Jacques              180             184             -4
 Birmingham Bulls          1976  Terry Ball                160             184            -24
 Birmingham Bulls          1976  John Garrett              175             184             -9
 Birmingham Bulls          1976  Jim Turkiewicz            185             184              1
 Birmingham Bulls          1976  Peter Folco               185             184              1
 Birmingham Bulls          1976  Dave Gorman               185             184              1
 Birmingham Bulls          1976  Dave Syvret               190             184              6
 Birmingham Bulls          1976  Jean-Luc Phaneuf          165             184            -19
 Birmingham Bulls          1976  Vaclav Nedomansky         205             184             21
 Birmingham Bulls          1976  John Stewart              180             184             -4
 Birmingham Bulls          1976  John Stewart              180             184             -4
 Birmingham Bulls          1976  Mark Napier               182             184             -2
 Birmingham Bulls          1976  Gilles Bilodeau           220             184             36
 Birmingham Bulls          1976  Ray McKay                 183             184             -1
 Birmingham Bulls          1976  Pat Westrum               185             184              1
 Birmingham Bulls          1976  Gord Gallant              175             184             -9
 Birmingham Bulls          1976  Tom Simpson               190             184              6
 Birmingham Bulls          1976  Jean-Guy Lagace           185             184              1
 Birmingham Bulls          1976  Keith Kokkola             210             184             26
 Birmingham Bulls          1976  Richard Farda             175             184             -9
 Birmingham Bulls          1976  Gavin Kirk                165             184            -19
 Birmingham Bulls          1976  Wayne Wood                190             184              6
 Birmingham Bulls          1976  Tim Sheehy                185             184              1
 Birmingham Bulls          1976  Dale Hoganson             190             184              6
 Birmingham Bulls          1976  Rick Cunningham           180             184             -4
 Birmingham Bulls          1976  Frank Mahovlich           205             184             21
 Birmingham Bulls          1976  Wayne Carleton            212             184             28
 Birmingham Bulls          1976  Buzz Schneider            175             184             -9
 Boston Bruins             1976  Earl Anderson             185             187             -2
 Boston Bruins             1976  Rick Smith                190             187              3
 Boston Bruins             1976  Dallas Smith              180             187             -7
 Boston Bruins             1976  Al Sims                   182             187             -5
 Boston Bruins             1976  Brad Park                 200             187             13
 Boston Bruins             1976  Clayton Pachal            185             187             -2
 Boston Bruins             1976  Matti Hagman              183             187             -4
 Boston Bruins             1976  Gerry Cheevers            185             187             -2
 Boston Bruins             1976  Gilles Gilbert            175             187            -12
 Boston Bruins             1976  Don Marcotte              183             187             -4
 Boston Bruins             1976  John Bucyk                215             187             28
 Boston Bruins             1976  Darryl Edestrand          180             187             -7
 Boston Bruins             1976  Rick Middleton            170             187            -17
 Boston Bruins             1976  Dave Forbes               180             187             -7
 Boston Bruins             1976  Gregg Sheppard            170             187            -17
 Boston Bruins             1976  Terry O'Reilly            200             187             13
 Boston Bruins             1976  Doug Halward              200             187             13
 Boston Bruins             1976  Hank Nowak                195             187              8
 Boston Bruins             1976  Jim Pettie                195             187              8
 Boston Bruins             1976  Ray Maluta                173             187            -14
 Boston Bruins             1976  Jean Ratelle              180             187             -7
 Boston Bruins             1976  Wayne Cashman             208             187             21
 Boston Bruins             1976  John Wensink              200             187             13
 Boston Bruins             1976  Stan Jonathan             175             187            -12
 Boston Bruins             1976  Bobby Schmautz            172             187            -15
 Boston Bruins             1976  Mike Milbury              200             187             13
 Boston Bruins             1976  Joe Zanussi               180             187             -7
 Boston Bruins             1976  Peter McNab               210             187             23
 Boston Bruins             1976  Gary Doak                 175             187            -12
...

We can also add an ORDER BY clause to sort the output by players that have the greatest weight difference.

SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
     , p.weight as PLAYER_WEIGHT
     , AVG(p.weight) OVER (PARTITION BY t.name ) AS AVG_TEAM_WEIGHT
     , p.weight - AVG(p.weight) OVER (PARTITION BY t.name ) AS WEIGHT_DIFF
 FROM players p, scoring s, teams t
 WHERE p.playerid= s.playerid
 AND s.teamid = t.teamid and s.year = t.year
 AND s.year = 1976
ORDER BY ABS(WEIGHT_DIFF) DESC;

        TEAM_NAME         YEAR      PLAYER_NAME      PLAYER_WEIGHT  AVG_TEAM_WEIGHT  WEIGHT_DIFF
 ------------------------- ----- -------------------- -------------- ---------------- ------------

 Calgary Cowboys           1976  Bruce Greig               220             183             37
 Calgary Cowboys           1976  Veli-Pekka Ketola         220             183             37
 Birmingham Bulls          1976  Gilles Bilodeau           220             184             36
 Winnipeg Jets             1976  Veli-Pekka Ketola         220             184             36
 Washington Capitals       1976  Rick Green                220             186             34
 Houston Aeros             1976  Steve West                150             183            -33
 Vancouver Canucks         1976  Bobby Lalonde             155             188            -33
 Montreal Canadiens        1976  Larry Robinson            225             192             33
 Buffalo Sabres            1976  Jerry Korab               220             188             32
 Phoenix Roadrunners       1976  Serge Beaudoin            215             183             32
 Vancouver Canucks         1976  Bob Dailey                220             188             32
 New England Whalers       1976  Doug Roberts              212             182             30
 Detroit Red Wings         1976  Bobby Sheehan             155             185            -30
 Philadelphia Flyers       1976  Bob Dailey                220             191             29
 New York Rangers          1976  Dave Tataryn              160             189            -29
 New York Islanders        1976  Jude Drouin               160             189            -29
 New York Rangers          1976  Gilles Gratton            160             189            -29
 Cleveland Barons          1976  Frank Spring              216             187             29
 Washington Capitals       1976  Harvey Bennett            215             186             29
 Birmingham Bulls          1976  Wayne Carleton            212             184             28
 San Diego Mariners        1976  Brent Hughes              205             177             28
 Edmonton Oilers           1976  Barry Long                210             182             28
 Vancouver Canucks         1976  Bruce Bullock             160             188            -28
 Phoenix Roadrunners       1976  Robbie Ftorek             155             183            -28
 Buffalo Sabres            1976  Don Edwards               160             188            -28
 Boston Bruins             1976  John Bucyk                215             187             28
 Houston Aeros             1976  Gary Donaldson            155             183            -28
 Calgary Cowboys           1976  Rich Lemieux              155             183            -28
 Minnesota North Stars     1976  Gary Smith                215             188             27
 New England Whalers       1976  Mike Antonovich           155             182            -27
 Buffalo Sabres            1976  Bill Hajt                 215             188             27
 Cleveland Barons          1976  Lyle Bradley              160             187            -27
 Edmonton Oilers           1976  Mike Antonovich           155             182            -27
 Atlanta Flames            1976  Eric Vail                 220             194             26
 Winnipeg Jets             1976  Barry Long                210             184             26
 Colorado Rockies          1976  Roger Lemelin             215             189             26
 Pittsburgh Penguins       1976  Russ Anderson             210             184             26
 Minnesota Fighting Saints 1976  Jack Carlson              205             179             26
 Washington Capitals       1976  Rick Bragnalo             160             186            -26
 New York Islanders        1976  Clark Gillies             215             189             26
 Pittsburgh Penguins       1976  Ron Stackhouse            210             184             26
 Birmingham Bulls          1976  Keith Kokkola             210             184             26
 San Diego Mariners        1976  Kevin Morrison            202             177             25
 Cleveland Barons          1976  Angelo Moretto            212             187             25
 New York Islanders        1976  Glenn Resch               165             189            -24
 Buffalo Sabres            1976  Joe Kowal                 212             188             24
 Birmingham Bulls          1976  Peter Marrin              160             184            -24
 Birmingham Bulls          1976  Terry Ball                160             184            -24
...
MAX()
MAX( expression ) OVER ( [ partition_clause ] order_by_clause )

The MAX() window function identifies the maximum value for expression argument. This expression can be defined as a table column, an arithmetic expression of columns and/or variables, etc.

The order_by_clause is optional. If it is provided, the function uses the order_by_clause to sort the rows before computing the maximum value of the expression for any given row in the window. This maximum expression value is assigned to each row of the partition group cumulatively. Therefore, the MAX() window function uses only the first row of the partition group to calculate the maximum expression value for that row. It uses the first two rows of the partition group to compute the maximum expression value returned for the second row. It uses the first three rows of the partition group to compute the maximum expression value returned for the third row, and so on.

If the order_by_clause is not provided, this maximum expression value is computed across all rows in the window and the result is assigned to each row in the window. In other words, each row in the window has the same maximum value.

If a partition_clause is provided, this maximum expression value computation is reset for the first row returned in each partition group, or window, based on the partition_clause.

The following example returns, for all teams in the NHL, each player’s weight and height. It uses the MAX() window function to return the maximum weight and height associated with each row in the window. The maximum expression value is computed for each row in the partition by using the current row plus all previous rows in the partition and sorted based on the order_by_clause.

Notice that the MAX() window function returns the same value for the 2nd and 3rd row when the order_by_clause column value within the partition window contains the same value, "Bill". The MAX() window function treats these rows the same for computing the row value. Adding the LastName column makes the order_by_clause value unique. The MAX() window function will compute each row value cumulatively.

SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
     , p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
     , MAX(p.weight) OVER (PARTITION BY t.name order by p.firstname) AS MAX_WEIGHT
     , MAX(p.weight) OVER (PARTITION BY t.name ORDER BY p.firstname,p.lastname) AS MAX_WEIGHT_2
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;
         TEAM_NAME         YEAR      PLAYER_NAME      PLAYER_WEIGHT  PLAYER_HEIGHT  MAX_WEIGHT  MAX_WEIGHT_2
 ------------------------- ----- -------------------- -------------- -------------- ----------- -------------

 Atlanta Flames            1976  Barry Gibbs               195             71           195          195
 Atlanta Flames            1976  Bill Clement              194             73           205          195
 Atlanta Flames            1976  Bill Flett                205             73           205          205
 Atlanta Flames            1976  Bobby Simpson             190             72           205          205
 Atlanta Flames            1976  Curt Bennett              195             75           205          205
 Atlanta Flames            1976  Dan Bouchard              190             72           205          205
 Atlanta Flames            1976  Dave Shand                200             74           205          205
 Atlanta Flames            1976  Ed Kea                    200             75           205          205
 Atlanta Flames            1976  Eric Vail                 220             73           220          220
 Atlanta Flames            1976  Guy Chouinard             182             71           220          220
 Atlanta Flames            1976  Hilliard Graves           175             71           220          220
 Atlanta Flames            1976  John Gould                197             71           220          220
 Atlanta Flames            1976  Ken Houston               210             74           220          220
 Atlanta Flames            1976  Larry Carriere            190             73           220          220
 Atlanta Flames            1976  Larry Romanchych          180             73           220          220
 Atlanta Flames            1976  Pat Quinn                 205             75           220          220
 Atlanta Flames            1976  Pat Ribble                210             76           220          220
 Atlanta Flames            1976  Phil Myre                 185             73           220          220
 Atlanta Flames            1976  Randy Manery              185             72           220          220
 Atlanta Flames            1976  Rey Comeau                190             68           220          220
 Atlanta Flames            1976  Richard Mulhern           188             73           220          220
 Atlanta Flames            1976  Rick Bowness              185             73           220          220
 Atlanta Flames            1976  Tim Ecclestone            195             70           220          220
 Atlanta Flames            1976  Tom Lysiak                185             73           220          220
 Atlanta Flames            1976  Willi Plett               205             75           220          220
 Birmingham Bulls          1976  Buzz Schneider            175             71           175          175
 Birmingham Bulls          1976  Dale Hoganson             190             70           190          190
 Birmingham Bulls          1976  Dave Gorman               185             71           190          190
 Birmingham Bulls          1976  Dave Syvret               190             72           190          190
 Birmingham Bulls          1976  Frank Mahovlich           205             72           205          205
 Birmingham Bulls          1976  Gavin Kirk                165             70           205          205
 Birmingham Bulls          1976  Gilles Bilodeau           220             73           220          220
 Birmingham Bulls          1976  Gord Gallant              175             71           220          220
 Birmingham Bulls          1976  Jean-Guy Lagace           185             70           220          220
 Birmingham Bulls          1976  Jean-Luc Phaneuf          165             68           220          220
 Birmingham Bulls          1976  Jeff Jacques              180             71           220          220
 Birmingham Bulls          1976  Jerry Rollins             195             74           220          220
 Birmingham Bulls          1976  Jim Marsh                 180             72           220          220
 Birmingham Bulls          1976  Jim Turkiewicz            185             70           220          220
 Birmingham Bulls          1976  John Garrett              175             68           220          220
 Birmingham Bulls          1976  John Stewart              180             72           220          220
 Birmingham Bulls          1976  John Stewart              180             72           220          220
 Birmingham Bulls          1976  Keith Kokkola             210             75           220          220
 Birmingham Bulls          1976  Lou Nistico               170             67           220          220
 Birmingham Bulls          1976  Mark Napier               182             70           220          220
 Birmingham Bulls          1976  Pat Westrum               185             70           220          220
 Birmingham Bulls          1976  Paul Heaver               195             73           220          220
 Birmingham Bulls          1976  Paul Henderson            180             71           220          220
 Birmingham Bulls          1976  Peter Folco               185             72           220          220
 Birmingham Bulls          1976  Peter Marrin              160             70           220          220
 Birmingham Bulls          1976  Ray McKay                 183             76           220          220
 Birmingham Bulls          1976  Rich Hart                 195             72           220          220
 Birmingham Bulls          1976  Richard Farda             175             69           220          220
 Birmingham Bulls          1976  Rick Cunningham           180             71           220          220
 Birmingham Bulls          1976  Terry Ball                160             69           220          220
 Birmingham Bulls          1976  Tim Sheehy                185             73           220          220
 Birmingham Bulls          1976  Tom Simpson               190             69           220          220
 Birmingham Bulls          1976  Vaclav Nedomansky         205             74           220          220
 Birmingham Bulls          1976  Wayne Carleton            212             75           220          220
 Birmingham Bulls          1976  Wayne Wood                190             73           220          220
 Boston Bruins             1976  Al Sims                   182             72           182          182
 Boston Bruins             1976  Bobby Schmautz            172             69           182          182
 Boston Bruins             1976  Brad Park                 200             72           200          200
 Boston Bruins             1976  Clayton Pachal            185             70           200          200
 Boston Bruins             1976  Dallas Smith              180             71           200          200
 Boston Bruins             1976  Darryl Edestrand          180             71           200          200
 Boston Bruins             1976  Dave Forbes               180             70           200          200
 Boston Bruins             1976  Don Marcotte              183             71           200          200
 Boston Bruins             1976  Doug Halward              200             73           200          200
 Boston Bruins             1976  Earl Anderson             185             73           200          200
 Boston Bruins             1976  Gary Doak                 175             71           200          200
 Boston Bruins             1976  Gerry Cheevers            185             71           200          200
 Boston Bruins             1976  Gilles Gilbert            175             73           200          200
 Boston Bruins             1976  Gregg Sheppard            170             68           200          200
 Boston Bruins             1976  Hank Nowak                195             73           200          200
 Boston Bruins             1976  Jean Ratelle              180             73           200          200
 Boston Bruins             1976  Jim Pettie                195             72           200          200
 Boston Bruins             1976  Joe Zanussi               180             70           200          200
 Boston Bruins             1976  John Bucyk                215             72           215          215
 Boston Bruins             1976  John Wensink              200             72           215          215
 Boston Bruins             1976  Matti Hagman              183             72           215          215
 Boston Bruins             1976  Mike Milbury              200             73           215          215
 Boston Bruins             1976  Peter McNab               210             75           215          215
 Boston Bruins             1976  Ray Maluta                173             68           215          215
 Boston Bruins             1976  Rick Middleton            170             71           215          215
 Boston Bruins             1976  Rick Smith                190             71           215          215
 Boston Bruins             1976  Stan Jonathan             175             68           215          215
 Boston Bruins             1976  Terry O'Reilly            200             73           215          215
 Boston Bruins             1976  Wayne Cashman             208             73           215          215
...
MIN()
MIN( expression ) OVER ( [ partition_clause ] order_by_clause )

The MIN() window function identifies the minimum value for the expression argument. This expression can be defined as a table column, an arithmetic expression of columns and/or variables, etc. This function behaves similar to the MAX window function.

The order_by_clause is optional. If it is provided, the function uses the order_by_clause to sort the rows before computing the minimum value of the expression for any given row in the window. This minimum expression value is assigned to each row of the partition group cumulatively. Therefore, the MIN() window function uses only the first row of the partition group to calculate the minimum expression value for that row. It uses the first two rows of the partition group to compute the minimum expression value returned for the second row. It uses the first three rows of the partition group to compute the minimum expression value returned for the third row, and so on.

If the order_by_clause is not provided, this minimum expression value is computed across all rows in the window and the result is assigned to each row in the window. In other words, each row in the window has the same minimum value.

If a partition_clause is provided, this minimum expression value computation is reset for the first row returned in each partition group, or window, based on the partition_clause.

The following example returns, for all teams in the NHL, each player’s weight and height. It uses the MIN() window function to return the minimum weight and height associated with each row in the window. The minimum expression value is computed for each row in the partition by using the current row plus all previous rows in the partition and sorted based on the order_by_clause.

SELECT t.name as TEAM_NAME, t.year, p.firstname||' '||p.lastname AS PLAYER_NAME
     , p.weight as PLAYER_WEIGHT, p.height as PLAYER_HEIGHT
     , MIN(p.weight) OVER (PARTITION BY t.name order by p.firstname,p.lastname) AS MIN_WEIGHT
FROM players p, scoring s, teams t
WHERE p.playerid= s.playerid
AND s.teamid = t.teamid and s.year = t.year
AND s.year = 1976;

          TEAM_NAME         YEAR      PLAYER_NAME      PLAYER_WEIGHT  PLAYER_HEIGHT  MIN_WEIGHT
 ------------------------- ----- -------------------- -------------- -------------- -----------

 Atlanta Flames            1976  Barry Gibbs               195             71           195
 Atlanta Flames            1976  Bill Clement              194             73           194
 Atlanta Flames            1976  Bill Flett                205             73           194
 Atlanta Flames            1976  Bobby Simpson             190             72           190
 Atlanta Flames            1976  Curt Bennett              195             75           190
 Atlanta Flames            1976  Dan Bouchard              190             72           190
 Atlanta Flames            1976  Dave Shand                200             74           190
 Atlanta Flames            1976  Ed Kea                    200             75           190
 Atlanta Flames            1976  Eric Vail                 220             73           190
 Atlanta Flames            1976  Guy Chouinard             182             71           182
 Atlanta Flames            1976  Hilliard Graves           175             71           175
 Atlanta Flames            1976  John Gould                197             71           175
 Atlanta Flames            1976  Ken Houston               210             74           175
 Atlanta Flames            1976  Larry Carriere            190             73           175
 Atlanta Flames            1976  Larry Romanchych          180             73           175
 Atlanta Flames            1976  Pat Quinn                 205             75           175
 Atlanta Flames            1976  Pat Ribble                210             76           175
 Atlanta Flames            1976  Phil Myre                 185             73           175
 Atlanta Flames            1976  Randy Manery              185             72           175
 Atlanta Flames            1976  Rey Comeau                190             68           175
 Atlanta Flames            1976  Richard Mulhern           188             73           175
 Atlanta Flames            1976  Rick Bowness              185             73           175
 Atlanta Flames            1976  Tim Ecclestone            195             70           175
 Atlanta Flames            1976  Tom Lysiak                185             73           175
 Atlanta Flames            1976  Willi Plett               205             75           175
 Birmingham Bulls          1976  Buzz Schneider            175             71           175
 Birmingham Bulls          1976  Dale Hoganson             190             70           175
 Birmingham Bulls          1976  Dave Gorman               185             71           175
 Birmingham Bulls          1976  Dave Syvret               190             72           175
 Birmingham Bulls          1976  Frank Mahovlich           205             72           175
 Birmingham Bulls          1976  Gavin Kirk                165             70           165
 Birmingham Bulls          1976  Gilles Bilodeau           220             73           165
 Birmingham Bulls          1976  Gord Gallant              175             71           165
 Birmingham Bulls          1976  Jean-Guy Lagace           185             70           165
 Birmingham Bulls          1976  Jean-Luc Phaneuf          165             68           165
 Birmingham Bulls          1976  Jeff Jacques              180             71           165
 Birmingham Bulls          1976  Jerry Rollins             195             74           165
 Birmingham Bulls          1976  Jim Marsh                 180             72           165
 Birmingham Bulls          1976  Jim Turkiewicz            185             70           165
 Birmingham Bulls          1976  John Garrett              175             68           165
 Birmingham Bulls          1976  John Stewart              180             72           165
 Birmingham Bulls          1976  John Stewart              180             72           165
 Birmingham Bulls          1976  Keith Kokkola             210             75           165
 Birmingham Bulls          1976  Lou Nistico               170             67           165
 Birmingham Bulls          1976  Mark Napier               182             70           165
 Birmingham Bulls          1976  Pat Westrum               185             70           165
 Birmingham Bulls          1976  Paul Heaver               195             73           165
 Birmingham Bulls          1976  Paul Henderson            180             71           165
 Birmingham Bulls          1976  Peter Folco               185             72           165
 Birmingham Bulls          1976  Peter Marrin              160             70           160
 Birmingham Bulls          1976  Ray McKay                 183             76           160
 Birmingham Bulls          1976  Rich Hart                 195             72           160
 Birmingham Bulls          1976  Richard Farda             175             69           160
 Birmingham Bulls          1976  Rick Cunningham           180             71           160
 Birmingham Bulls          1976  Terry Ball                160             69           160
 Birmingham Bulls          1976  Tim Sheehy                185             73           160
 Birmingham Bulls          1976  Tom Simpson               190             69           160
 Birmingham Bulls          1976  Vaclav Nedomansky         205             74           160
 Birmingham Bulls          1976  Wayne Carleton            212             75           160
 Birmingham Bulls          1976  Wayne Wood                190             73           160
 Boston Bruins             1976  Al Sims                   182             72           182
 Boston Bruins             1976  Bobby Schmautz            172             69           172
 Boston Bruins             1976  Brad Park                 200             72           172
 Boston Bruins             1976  Clayton Pachal            185             70           172
 Boston Bruins             1976  Dallas Smith              180             71           172
 Boston Bruins             1976  Darryl Edestrand          180             71           172
 Boston Bruins             1976  Dave Forbes               180             70           172
 Boston Bruins             1976  Don Marcotte              183             71           172
 Boston Bruins             1976  Doug Halward              200             73           172
 Boston Bruins             1976  Earl Anderson             185             73           172
 Boston Bruins             1976  Gary Doak                 175             71           172
 Boston Bruins             1976  Gerry Cheevers            185             71           172
 Boston Bruins             1976  Gilles Gilbert            175             73           172
 Boston Bruins             1976  Gregg Sheppard            170             68           170
 Boston Bruins             1976  Hank Nowak                195             73           170
 Boston Bruins             1976  Jean Ratelle              180             73           170
 Boston Bruins             1976  Jim Pettie                195             72           170
 Boston Bruins             1976  Joe Zanussi               180             70           170
 Boston Bruins             1976  John Bucyk                215             72           170
 Boston Bruins             1976  John Wensink              200             72           170
 Boston Bruins             1976  Matti Hagman              183             72           170
 Boston Bruins             1976  Mike Milbury              200             73           170
 Boston Bruins             1976  Peter McNab               210             75           170
 Boston Bruins             1976  Ray Maluta                173             68           170
 Boston Bruins             1976  Rick Middleton            170             71           170
 Boston Bruins             1976  Rick Smith                190             71           170
 Boston Bruins             1976  Stan Jonathan             175             68           170
 Boston Bruins             1976  Terry O'Reilly            200             73           170
 Boston Bruins             1976  Wayne Cashman             208             73           170
...