SUM window function
The SUM window function returns the sum of the input column or expression values. The SUM function works with numeric values and ignores NULL values.
Syntax
SUM ( [ ALL ] expression ) OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list frame_clause ] )
Arguments
 expression

The target column or expression that the function operates on.
 ALL

With the argument ALL, the function retains all duplicate values from the expression. ALL is the default. DISTINCT is not supported.
 OVER

Specifies the window clauses for the aggregation functions. The OVER clause distinguishes window aggregation functions from normal set aggregation functions.
 PARTITION BY expr_list

Defines the window for the SUM function in terms of one or more expressions.
 ORDER BY order_list

Sorts the rows within each partition. If no PARTITION BY is specified, ORDER BY uses the entire table.
 frame_clause

If an ORDER BY clause is used for an aggregate function, an explicit frame clause is required. The frame clause refines the set of rows in a function's window, including or excluding sets of rows within the ordered result. The frame clause consists of the ROWS keyword and associated specifiers. See Window function syntax summary.
Data types
The argument types supported by the SUM function are SMALLINT, INTEGER, BIGINT, NUMERIC, DECIMAL, REAL, and DOUBLE PRECISION.
The return types supported by the SUM function are:

BIGINT for SMALLINT or INTEGER arguments

NUMERIC for BIGINT arguments

DOUBLE PRECISION for floatingpoint arguments
Examples
The following example creates a cumulative (rolling) sum of sales quantities ordered by date and sales ID:
select salesid, dateid, sellerid, qty, sum(qty) over (order by dateid, salesid rows unbounded preceding) as sum from winsales order by 2,1; salesid  dateid  sellerid  qty  sum ++++ 30001  20030802  3  10  10 10001  20031224  1  10  20 10005  20031224  1  30  50 40001  20040109  4  40  90 10006  20040118  1  10  100 20001  20040212  2  20  120 40005  20040212  4  10  130 20002  20040216  2  20  150 30003  20040418  3  15  165 30004  20040418  3  20  185 30007  20040907  3  30  215 (11 rows)
For a description of the WINSALES table, see Overview example for window functions.
The following example creates a cumulative (rolling) sum of sales quantities by date, partition the results by seller ID, and order the results by date and sales ID within the partition:
select salesid, dateid, sellerid, qty, sum(qty) over (partition by sellerid order by dateid, salesid rows unbounded preceding) as sum from winsales order by 2,1; salesid  dateid  sellerid  qty  sum ++++ 30001  20030802  3  10  10 10001  20031224  1  10  10 10005  20031224  1  30  40 40001  20040109  4  40  40 10006  20040118  1  10  50 20001  20040212  2  20  20 40005  20040212  4  10  50 20002  20040216  2  20  40 30003  20040418  3  15  25 30004  20040418  3  20  45 30007  20040907  3  30  75 (11 rows)
The following example numbers all of the rows sequentially in the result set, ordered by the SELLERID and SALESID columns:
select salesid, sellerid, qty, sum(1) over (order by sellerid, salesid rows unbounded preceding) as rownum from winsales order by 2,1; salesid  sellerid  qty  rownum +++ 10001  1  10  1 10005  1  30  2 10006  1  10  3 20001  2  20  4 20002  2  20  5 30001  3  10  6 30003  3  15  7 30004  3  20  8 30007  3  30  9 40001  4  40  10 40005  4  10  11 (11 rows)
For a description of the WINSALES table, see Overview example for window functions.
The following example numbers all rows sequentially in the result set, partition the results by SELLERID, and order the results by SELLERID and SALESID within the partition:
select salesid, sellerid, qty, sum(1) over (partition by sellerid order by sellerid, salesid rows unbounded preceding) as rownum from winsales order by 2,1; salesid  sellerid  qty  rownum +++ 10001  1  10  1 10005  1  30  2 10006  1  10  3 20001  2  20  1 20002  2  20  2 30001  3  10  1 30003  3  15  2 30004  3  20  3 30007  3  30  4 40001  4  40  1 40005  4  10  2 (11 rows)