Compute a Running Total in Postgresql

PostgreSQL is a modern relational database system that is capable of both SQL-oriented relational queries and JSON non-relational queries. It is free and open source. Running total or cumulative sum are very frequent tasks in PostgreSQL, which can be used to deal with sequential data or financial records with a long history of entries.

It consists of the additional together of the contents of a specific column; running through the rows with a defined sequence of a table. Undoubtedly we can consider PostgreSQL to possess certain tools on the high level of performance. In this article, we will see what the running total is and different ways to calculate the running total in PostgreSQL.

What is Running Total?

A running total is the sum of a series of values or the accumulative sum linked to values. Adding a new value is quite easy. It is just a matter of adding all the previously calculated values A running total is commonly used in reporting and data analysis to provide cumulative analytics. For example, the current total of sales would be based on the sum of the sales of the first sale, second sale, third sale, and so on.

We can calculate the running total using two methods:

Using Window Functions
Using Recursive Common Table Expressions (CTE)

First, let’s create the table and insert some sample data:

Create the table named sales

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    date DATE,
    amount NUMERIC
);

Insert some data into the table

INSERT INTO sales (date, amount) VALUES
    ('2024-04-01', 100),
    ('2024-04-02', 150),
    ('2024-04-03', 200),
    ('2024-04-04', 250),
    ('2024-04-05', 300);

Output:

You can see the content of the table by executing the below command:

SELECT * FROM employees;

Sales Table

Now, let’s compute the running total using both window functions and recursive CTEs.

1. Using Window Functions

Window functions in PostgreSQL allow you to perform calculations across a set of rows related to the current row. The SUM() function combined with the OVER() clause can be used to compute a running total.

Compute running total using window function:

SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM
    sales
ORDER BY
    date;

Explanation:

SELECT statement: It selects the date, amount, and the running total column.
SUM(amount) OVER (ORDER BY date) AS running_total: The SUM() function computes the cumulative sum of the amount column. The OVER() clause specifies a window frame over which the function operates. In this case, it’s ordering the rows by the date column. ORDER BY date ensures that the sum is calculated in chronological order based on the date column. The result of this computation is aliased as running_total.
FROM sales: Specifies the table from which the data is being retrieved.
ORDER BY date: Orders the result set by the date column in ascending order.

Output:

The output of this query will be a result set containing three columns: date, amount, and running_total. Each row will represent an expense record from the expenses table. The running_total column will contain the cumulative sum of the amount column up to that particular row, ordered by the date.

Using Window Function

Using Recursive Common Table Expressions (CTE)

Another approach is to use recursive common table expressions (CTEs). This method is useful when dealing with complex calculations or in scenarios where window functions may not be applicable.

Here’s how you can compute the running total using a recursive CTE:

WITH RECURSIVE cte_sales AS (
    SELECT
        date,
        amount,
        amount AS running_total
    FROM
        sales
    WHERE
        date = (SELECT MIN(date) FROM sales)
    UNION ALL
    SELECT
        s.date,
        s.amount,
        cte.running_total + s.amount AS running_total
    FROM
        cte_sales cte
    JOIN
        sales s ON s.date = (SELECT MIN(date) FROM sales WHERE date > cte.date)
)
SELECT
    date,
    amount,
    running_total
FROM
    cte_sales
ORDER BY
    date;

Explanation:

Recursive CTE definition (WITH RECURSIVE cte_sales AS …):

The CTE is named cte_sales.
The initial (non-recursive) part selects the earliest date from the sales table and sets the running_total to the amount for that date.
The recursive part calculates the running total for subsequent dates by adding the current amount to the running total from the previous row.
The recursion continues until all rows in the sales table are processed.

Non-recursive part:

The initial query selects the earliest date from the sales table using (SELECT MIN(date) FROM sales) and initializes the running_total to the amount for that date.

Recursive part:

The recursive part joins the CTE (cte_sales) with the sales table (s) based on dates.
For each row in the CTE, it finds the next chronological date from the sales table and calculates the running total by adding the current amount to the running total from the previous row.

Final SELECT statement:

Retrieves the date, amount, and running_total columns from the cte_sales.
Orders the result set by the date.

Output:

The output is the same as above.

Using CTE

Conclusion

A running total for PostgreSQL involves using some different approaches that can be utilized to accomplish this fundamental operation. While both the fixed window functions and the recursive CTEs suit different individuals, PostgreSQL ensures you have all these capabilities in it. Based on such factors as use case requirements and performance expectancy, you can go for the method that conforms to your preference.

Article Tags :

Databases

PostgreSQL