Overview
The SUM() window function returns the sum of the input column or expression values. It can be used with a RANGE clause, that allows you to define a logical frame of rows based on the values of the current row, rather than a fixed number of rows.
Syntax
The syntax for this function is as follows:
SUM(expression) OVER (
[PARTITION BY partition_expression]
ORDER BY sort_expression
[ROWS | RANGE BETWEEN start_value AND end_value]
)
The expression’s argument types supported by the SUM window function are INTEGER, BIGINT, REAL and DOUBLE PRECISION. The return types of the SUM function are: BIGINT for integer and DOUBLE PRECISION for floating-point arguments.
The SUM() window function works with numeric values and ignores NULL ones
Parameters
expression: input’s column or expression values to be summedPARTITION BY: optional clause, which divides the result set into partitions to which the function is appliedROWS | RANGE BETWEEN: range-based window frame relative to the current row
Examples
For the needs of this section, we will create the winsales table that stores details of some sales transactions:
CREATE TABLE winsales(
salesid int,
dateid date,
sellerid int,
buyerid text,
qty int,
qty_shipped int);
INSERT INTO winsales VALUES
(30001, '8/2/2003', 3, 'b', 10, 10),
(10001, '12/24/2003', 1, 'c', 10, 10),
(10005, '12/24/2003', 1, 'a', 30, null),
(40001, '1/9/2004', 4, 'a', 40, null),
(10006, '1/18/2004', 1, 'c', 10, null),
(20001, '2/12/2004', 2, 'b', 20, 20),
(40005, '2/12/2004', 4, 'a', 10, 10),
(20002, '2/16/2004', 2, 'c', 20, 20),
(30003, '4/18/2004', 3, 'b', 15, null),
(30004, '4/18/2004', 3, 'b', 20, null),
(30007, '9/7/2004', 3, 'c', 30, null);
SUM() with ORDER BY
In this example, we will focus on executing the SUM() window function with ORDER BY keyword:
SELECT salesid, dateid, sellerid, qty
SUM(qty) OVER (ORDER BY dateid, salesid ROWS UNBOUNDED PRECEDING)
FROM winsales
ORDER BY 2,1;
The output from the above query includes the sales ID, date ID, seller ID, quantity and quantity sum:
salesid | dateid | sellerid | qty | sum
---------+------------+----------+-----+-----
30001 | 2003-08-02 | 3 | 10 | 10
10001 | 2003-12-24 | 1 | 10 | 20
10005 | 2003-12-24 | 1 | 30 | 50
40001 | 2004-01-09 | 4 | 40 | 90
10006 | 2004-01-18 | 1 | 10 | 100
20001 | 2004-02-12 | 2 | 20 | 120
40005 | 2004-02-12 | 4 | 10 | 130
20002 | 2004-02-16 | 2 | 20 | 150
30003 | 2004-04-18 | 3 | 15 | 165
30004 | 2004-04-18 | 3 | 20 | 185
30007 | 2004-09-07 | 3 | 30 | 215
(11 rows)
SUM() with ORDER BY and ROWS Frame
In this example we will calculate the running total of qty ordered by dateid and salesid using a ROWS UNBOUNDED PRECEDING frame,
which sums all rows from the start up to the current row:
SELECT salesid, dateid, sellerid, qty,
SUM(qty) OVER (ORDER BY dateid, salesid ROWS UNBOUNDED PRECEDING) AS running_qty_sum
FROM winsales
ORDER BY dateid, salesid;
After executing the query above, we get the following output:
salesid | dateid | qty | running_qty_sum
---------+------------+-----+-----------------
30001 | 2003-08-02 | 10 | 10
10001 | 2003-12-24 | 10 | 20
10005 | 2003-12-24 | 30 | 50
40001 | 2004-01-09 | 40 | 90
10006 | 2004-01-18 | 10 | 100
20001 | 2004-02-12 | 20 | 120
40005 | 2004-02-12 | 10 | 130
20002 | 2004-02-16 | 20 | 150
30003 | 2004-04-18 | 15 | 165
30004 | 2004-04-18 | 20 | 185
30007 | 2004-09-07 | 30 | 215
(11 rows)
The running_qty_sum column shows the cumulative sum of qty ordered by dateid and salesid.
For each row, it sums all qty values from the first row up to the current row in that order.
SUM() with ORDER BY and PARTITION BY
In this example we will focus on executing the SUM() function with ORDER BY keyword and PARTITION BY clause:
SELECT salesid, dateid, sellerid, qty
SUM(qty) OVER (PARTITION BY sellerid ORDER BY dateid, sellerid ROWS UNBOUNDED PRECEDING)
FROM winsales
ORDER BY 3,2,1;
After executing the query above, we get the following output:
salesid | dateid | sellerid | qty | sum
---------+------------+----------+-----+-----
10001 | 2003-12-24 | 1 | 10 | 10
10005 | 2003-12-24 | 1 | 30 | 40
10006 | 2004-01-18 | 1 | 10 | 50
20001 | 2004-02-12 | 2 | 20 | 20
20002 | 2004-02-16 | 2 | 20 | 40
30001 | 2003-08-02 | 3 | 10 | 10
30003 | 2004-04-18 | 3 | 15 | 25
30004 | 2004-04-18 | 3 | 20 | 45
30007 | 2004-09-07 | 3 | 30 | 75
40001 | 2004-01-09 | 4 | 40 | 40
40005 | 2004-02-12 | 4 | 10 | 50
(11 rows)
Time Series: SUM() with RANGE BETWEEN for Last 30 Days
In this example, we will demonstrate a common time series use case.
Calculating the rolling sum of sales quantity over the last 30 days for each row,
using the RANGE BETWEEN INTERVAL ‘30 days’ PRECEDING AND CURRENT ROW frame:
SELECT salesid, dateid, qty,
SUM(qty) OVER (
ORDER BY dateid
RANGE BETWEEN INTERVAL '30 days' PRECEDING AND CURRENT ROW
) AS rolling_30d_qty_sum
FROM winsales
ORDER BY dateid;
The output from the above query sums the qty of all sales within the 30-day window ending at the current row’s dateid:
salesid | dateid | qty | rolling_30d_qty_sum
---------+------------+-----+---------------------
30001 | 2003-08-02 | 10 | 10
10001 | 2003-12-24 | 10 | 40
10005 | 2003-12-24 | 30 | 40
40001 | 2004-01-09 | 40 | 80
10006 | 2004-01-18 | 10 | 90
20001 | 2004-02-12 | 20 | 40
40005 | 2004-02-12 | 10 | 40
20002 | 2004-02-16 | 20 | 60
30003 | 2004-04-18 | 15 | 35
30004 | 2004-04-18 | 20 | 35
30007 | 2004-09-07 | 30 | 30
(11 rows)
