How to Calculate a Rolling Sum in PostgreSQL (With Example)

A rolling sum is the sum of a set of numbers during a specific time period.

Often in PostgreSQL you may want to calculate a rolling sum for a particular column in a table.

You can use the following basic syntax to do so:

SELECT *,
       SUM(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rolling_sum
FROM sales;

This particular example will calculate the rolling sum of the current row and the 2 previous rows in the column named sales_amount of the table named sales.

To use a different number of periods for the rolling sum, simply change the 2 to a different number.

The following example shows how to use this syntax in practice.

Example: How to Calculate a Rolling Sum in PostgreSQL

Suppose that we create the following table named sales that contains information about the total number of sales made by an employee at a company during eight consecutive days:

-- create table named 'sales'
CREATE TABLE sales (
  sales_date DATE,
  sales_amount INT
);

-- add rows to the table
INSERT INTO sales VALUES ('2024-01-01', 22);
INSERT INTO sales VALUES ('2024-01-02', 25);
INSERT INTO sales VALUES ('2024-01-03', 28);
INSERT INTO sales VALUES ('2024-01-04', 36);
INSERT INTO sales VALUES ('2024-01-05', 30);
INSERT INTO sales VALUES ('2024-01-06', 45);
INSERT INTO sales VALUES ('2024-01-07', 42);
INSERT INTO sales VALUES ('2024-01-08', 39);

-- view all rows in table
SELECT * FROM sales;

 sales_date | sales_amount 
------------+--------------
 2024-01-01 |           22
 2024-01-02 |           25
 2024-01-03 |           28
 2024-01-04 |           36
 2024-01-05 |           30
 2024-01-06 |           45
 2024-01-07 |           42
 2024-01-08 |           39

Suppose that we would like to calculate a 3-day rolling sum of values in the sales_amount column.

We can use the following syntax to do so:

SELECT *,
       SUM(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rolling_sum
FROM sales;

 sales_date | sales_amount | rolling_sum 
------------+--------------+-------------
 2024-01-01 |           22 |          22
 2024-01-02 |           25 |          47
 2024-01-03 |           28 |          75
 2024-01-04 |           36 |          89
 2024-01-05 |           30 |          94
 2024-01-06 |           45 |         111
 2024-01-07 |           42 |         117
 2024-01-08 |           39 |         126

The new column named rolling_sum contains the 3-day rolling sum of sales in the sales_amount column.

For example, we can see:

• The 3-day rolling sum of sales on 2024-01-03 was 22+25+28 = 75.
• The 3-day rolling sum of sales on 2024-01-04 was 25+28+36 = 89.
• The 3-day rolling sum of sales on 2024-01-05 was 28+36+30 = 94.

And so on.

Note: For rows that did not have at least 2 previous periods, the rolling sum was simply calculated using as many days that were available.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Extract Date from a Timestamp
PostgreSQL: How to Extract Month from Date