How to use Recursive Common Table Expressions (CTE) In SQL

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.

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.