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;
Now, let’s compute the running total using both window functions and recursive CTEs.
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.