> ## Documentation Index > Fetch the complete documentation index at: https://docs.oxla.com/llms.txt > Use this file to discover all available pages before exploring further. # SUM() ## 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: ```sql theme={null} 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 summed * `PARTITION BY`: optional clause, which divides the result set into partitions to which the function is applied * `ROWS | 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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`: ```sql theme={null} 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) ```