Cloud

sum

The sum() window function returns the sum of the input column or expression values. It can be used with a RANGE clause, that defines 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:

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: Column or expression to sum.

  • PARTITION BY: Optional. Divides the result set into partitions, each processed independently. If omitted, the entire result set is treated as a single partition.

  • ROWS | RANGE BETWEEN: Range-based window frame relative to the current row.

Examples

The following examples use 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

This example executes 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 this 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

This example calculates 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;

The query returns:

 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

This example executes 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;

The query returns:

 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

This example demonstrates 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 this 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)