How to Calculate Cumulative Sum-Running Total in PostgreSQL

In PostgreSQL, calculating cumulative sums or running totals is a common operation that can be achieved using window functions. These functions allow you to perform calculations across a set of table rows that are related to the current row. In this tutorial, we’ll explore how to calculate a cumulative sum using the SUM() function combined with the OVER() clause.

What is a Running Total?

A running total, or cumulative sum, is a sequence of partial sums of a given data set. For example, in a list of daily sales, a running total would show the total sales up to each day. This can be particularly useful in financial reports or time-based data analysis.

Step 1: Prepare Your Data

Let’s start by creating a sample table with some sales data.

-- Create a sample sales table
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sales_date DATE,
    amount DECIMAL
);

-- Insert sample data
INSERT INTO sales (sales_date, amount) VALUES
('2025-01-01', 100.00),
('2025-01-02', 200.00),
('2025-01-03', 150.00),
('2025-01-04', 50.00);
                            

Step 2: Calculate Cumulative Sum

Now that we have our sales data, let’s calculate the cumulative sum using the SUM() window function. The OVER() clause will allow us to accumulate the values over a specific order.

-- Calculate the running total
SELECT
    sales_date,
    amount,
    SUM(amount) OVER (ORDER BY sales_date) AS running_total
FROM sales
ORDER BY sales_date;
                            

Explanation

• The SUM(amount) function calculates the sum of the amount column.
• The OVER (ORDER BY sales_date) clause defines the window for the calculation, ordering the rows by sales_date so that the sum is calculated sequentially.
• The running_total column shows the cumulative sum at each point in time.

Result

The query will return a result similar to the following:

 sales_date | amount | running_total
------------+--------+--------------
 2025-01-01 | 100.00 |       100.00
 2025-01-02 | 200.00 |       300.00
 2025-01-03 | 150.00 |       450.00
 2025-01-04 |  50.00 |       500.00
                            

Step 3: Adding Partitioning (Optional)

In some cases, you may want to calculate the running total within a specific category, such as a department or a region. You can do this by using the PARTITION BY clause. For example, let’s say we have multiple regions, and we want to calculate the running total per region.

-- Add partitioning to calculate running total by region
SELECT
    region,
    sales_date,
    amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY sales_date) AS running_total
FROM sales
ORDER BY region, sales_date;
                            

Conclusion

In this tutorial, we’ve learned how to calculate the cumulative sum or running total in PostgreSQL using the SUM() function with the OVER() clause. By using window functions, you can easily calculate running totals over any ordered or partitioned data set, making it an essential tool for data analysis.

For more advanced SQL techniques and tutorials, stay tuned to our blog!