What Is A SQL Running Total And How To Compute It?

Would you like to discover what a running total is and how to calculate it using SQL? The SQL running total is a very popular pattern that is commonly used in trend research and finance. You will discover what a running total is in this article, along with how to create a SQL query to calculate it. Also, we’ll go over various business situations when a running total is required.

What is Running Total?

A running total in SQL is a calculation that displays the cumulative total of a particular column in a table as rows are processed in the query result. It is often referred to as a running sum or cumulative sum.

As an example, visualise yourself working in sales and keeping track of how many products were sold on a specific day. The total number of products sold up to a particular date should be calculated as a running total. The total number of products sold on each day is listed in the table below:

The running total is shown in the third column. Remember that the running total includes the value of the current row at all times. For example, the running total on May 4, 2022, is 69. This represents the total number of products sold between May 1, 2022, and May 4, 2022 (inclusive). The calculation is as follows: 14 + 18 + 10 + 27 = 69.

There are window functions that let us calculate a running total in relational databases (like SQL Server, Oracle, PostgreSQL, and MySQL) and even non-relational engines (like Hive and Presto).

How to Compute Running Total in SQL?

Window functions are used in SQL to compute the running total. With the help of this unique SQL capability, complicated calculations can be performed with just a few lines of SQL code. You must be familiar with the window functions that your database offers if you want to compute the running total in SQL. Window functions work on a set of rows and produce an aggregate value for each row in the result set.

The SQL window function’s syntax for computing a cumulative sum across rows is as follows:

SELECT *,
      SUM(no_of_products) OVER(ORDER BY date) AS running_total
FROM SALESCode language: SQL (Structured Query Language) (sql)

Let’s see the description of the above query:

• The OVER denotes that this is a window function.
• There is a ORDER BY clause inside the OVER clause. This information reveals the row ordering prior to the calculation. In our example, the date column determines the order of the rows.
• When employing window functions, each record receives a unique calculation. So, the result is shown with a second column. When using window functions, no rows are compressed. This is one of the primary distinctions between SQL’s GROUP BY clause and window functions.
• When using window functions, we define a window frame for every record in a table, inside of which a particular computation is performed. The ROW/RANGE term in the OVER statement indicates this. A default value for the window frame is used if this keyword is not defined (as in the case of our example). The current row and all preceding rows are included in a current-row default window frame.
• In our example, the total sum of every record in the table is calculated by adding the most recent value to all previous ones. For example, the running total as of May 4, 2022, is 69. The value in the current row i.e., 27 is added to the three previous values (14,18 and 10).

Also Read: Differences between GROUP BY clause and window functions.

Example 1:

In this example, we’re going to calculate the daily running total of all the users who have registered.

The SQL query for calculating the daily running total is as follows:

SELECT date_of_registration, users_registered,
  SUM(users_registered) OVER (ORDER BY date_of_registration )
  AS total_no_of_users
FROM Users;Code language: SQL (Structured Query Language) (sql)

The registration date for each user is selected by this query. Additionally, we require the total number of users from the first given day (2023-03-01) to the last day in a row. The result set is as follows:

Here, We use the SUM() aggregate function to calculate the running total and pass the column users_registered as the parameter since we want to get the total number of users from this column. Utilizing the OVER clause is the next step. This clause’s argument in our case is ORDER BY date_of_registration. This field (date_of_registration) determines the order in which the rows of the result set are displayed. The current value (i.e., users registered on the day of the current row) is added to the previous value (i.e., the total number of users prior to the date in the current row) for each value in the date_of_registration column.

Take note that the new column we named total_no_of_users displays the total sum. We have 12 registered users as of the registration date 2023-03-01 in the first step. The total number of users who registered, for now, is 12. The next step involves adding to this total (12). What do we add? As we have 15 members registered as of date (2023-03-03), giving us a rolling total of 27. In the final row of the result set for the most recent registration date, 2023-03-03, we get the running_total as 37.

Example 2:

Let’s see how running total is used in some real-life scenarios. In this example, we will talk about how running total can be used in calculating a balance. The cumulative sum is updated each time a new transaction (a deposit into or withdrawal from an account) is made, and the current balance is shown. Let’s look at the below balance table:

We can see from the table above that the initial transaction, a Rs. 4500 inflow, took place on February 1st, 01-02-2023. On this day, there was a Rs. 4500 balance. The client then started making purchases. The client made a Rs. 250 purchase on Feb 3, 2023(this transaction is displayed with a negative value). Consequently, the balance dropped to Rs. 4250. The total dropped by another Rs. 100 the following day to Rs. 4150, and so on. The running total is used to determine the account balance. It represents the total of all transactions related to that account. The balance is updated, or the running total is revised, with each new transaction.

The SQL query for this example is as follows:

SELECT transaction_date, transaction_amount,
           SUM(transaction_amount) OVER(order by transaction_date) 
           AS balance_amount
FROM Balance;Code language: SQL (Structured Query Language) (sql)

Summary

We explored how running totals are calculated and applied in several situations in this article. Running totals have a variety of uses, as you have learnt. Running totals can be calculated with simply a few lines of code using SQL window functions. When working with complicated calculations, window functions are employed. So, window functions are a must to learn. You will produce cleaner, more comprehensible code after you understand the syntax. As we demonstrated how to use window functions for running total computations, window functions are also frequently used for a wide range of calculations. Hope you enjoyed reading this tutorial.

Reference

https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server