7 Real-Life Situations When You Need a Running Total and How to Compute It in SQL

Do you want to learn what a running total is and how to compute it SQL? In this article, I will describe different business scenarios in which a running total is needed. I will also teach you how to calculate a running total in SQL with the help of window functions. Ready? Let’s go!

Running Total Definition

A running total is the cumulative sum of a value and all previous values in the column.

For example, imagine you are in sales and storing information about the number of items sold on a particular day. You might want to calculate a running total, the total number of items sold up to a specific date.

Below is a table with the number of items sold on a particular day:

Number of sold items and running total

The third column is the calculated running total. Keep in mind that the current row value is always included in the running total.

For example, on 05 Jan 2021, the running total is 66. This is the total number of items sold from 01 Jan 2021 to 05 Jan 2021 (including on 05 Jan 2021). Specifically, the calculation is 10 + 12 + 15 + 9 + 20 = 66.

Running Total Calculation in SQL

In SQL, the running total is calculated with the help of window functions. This special SQL feature can do complex calculations with only a few lines of SQL code.

Here is the window function for our example above:

SELECT *,
      SUM(no_of_items) OVER(ORDER BY date) AS running_total 
FROM sales

And here is an explanation of the code:

• ? This is a window function, denoted with the OVER clause.
• Inside the OVER clause, there is an ORDER BY This tells us how the rows are ordered before the calculation is made. In our case, the rows are ordered by the date column.
• When using window functions, each record gets its own calculation. So, the result is displayed with one additional column. There are no collapsed rows when working with window functions. This is the main difference between the GROUP BY clause and window functions in SQL.
• Inside the OVER statement, you might also find the PARTITION BY keyword. PARTITION BY groups rows into partitions in which each partition calculation is made separately. In our example, the rows are not organized into partitions. So, the whole data set is treated as one group.
• When working with window functions, for each record in a table, we define a window frame inside which a specific calculation is made. This is denoted by the ROW/RANGE keyword inside the OVER statement. If this keyword is not defined (as in our example), a default value for the window frame is taken. A current-row default window frame includes the current row and all previous rows.
• For our example, each record in the table’s total sum is calculated by summing together all previous values plus the current one. For example, the running total for 05 Jan 2021 is 66. ?This is the sum of the four previous values (10, 12, 15, and 9) plus the value in the current row (20).

Running Total Examples

Okay, let’s take a look at real-life scenarios in which running totals are used.

1.   Keeping Track of Planned vs. Realized Quotas

In sales, each salesperson must meet goals to satisfy specific job requirements. Similarly, in the telecommunication and banking industries, each division must acquire a specific number of new clients in each quarter and sell a specific number of products to clients.

Management tracks performance using running totals. Each day, the running total is updated with new data. These numbers are usually evaluated monthly, quarterly, or yearly.

Here is a table that tracks the number of new loans in 2021 on a monthly basis:

Number of new loans, planed vs. realized

For the first half of 2021, it is decided that 535 loans should be sold to clients in this timespan. Plans are made for each month separately, and 535 is the running total goal for these six months.

Management tracks the planned vs. realized quotas. For now, there are 112 new loans sold. The table will continue to be updated as time passes.

Below is the SQL code that calculates the running totals:

SELECT 
      date,
      plan,
      SUM(plan) OVER(order by Date) AS plan_running_total,
      realization,
      SUM(realization) OVER(order by Date) AS realization_running_total
FROM sales;

As in the previous example, a window function is used for calculating the running total. The window function is denoted with OVER, and inside the brackets, the order of the rows is defined.

The aggregation function used is SUM. We have two running totals: ?planned and realized quotas. So, we have two window functions, and two columns are added to the table.

2. Balance Calculations

Running totals are also used when calculating a balance. Each time when a new transaction is made (a payment to or withdrawal from an account), the cumulative sum is refreshed, and the current balance is displayed.

Below is a balance table:

Transaction amount and current balance

In the table above, we see that the first transaction occurred on 01 Dec 2020–12–01, an inflow of $5,000. The balance on this day was $5,000. After, the client started to spend money.

On 03 Dec 2020, the client spent $50 (this transaction is shown with a negative value). So, the balance decreased to $4,950. On the next day, the balance decreased by an additional $125 to $4,825, and so on.

The account balance is calculated as a running total. It is the cumulative sum of all of the transactions associated with that account. With each new transaction, the balance is updated, that is, the running total is recalculated.

Here is the window function for this example:

SELECT date,
            transaction,
           SUM(transaction) OVER(order by date) AS balance_amount 
FROM balance;

3. Cash Register Operations

Running totals are also used in cash register operations.

For example, when a customer is purchasing items in a store, the cashier scans the items in the basket. With each newly scanned item, the updated running total is displayed on the screen. This shows how much the customer needs to pay for the items scanned so far.

Below is an example of a basket in the process of being scanned. Diapers are the most recently scanned item. So far, the customer needs to pay 19.70 EUR. Shampoo has not been processed yet, so its price is not yet calculated in the running total:

Cash register: scanning products

This is almost the same window function as in the previous example (only the table and column names differ):

SELECT 
       *,
      SUM(price) OVER(order by date) as running_total 
FROM cash_register;

So, each time the cashier scans an item, the cumulative sum is updated. With each scan, the cashier knows how much the customer needs to pay so far.

4. Counting Daily Calorie Intake

If you wanted to lose some weight, you could use a running total to calculate your daily calorie intake. Each day, you would start your calorie count at zero and then update the running total based on what and how much you eat.

Below is a calorie count for one day:

The running total is updated every time you eat something. So, you always know how much you already ate and how much more you can eat that day. This approach is called the calorie count diet, which is a popular option.

Here is the code that calculates the running total:

SELECT calorie_intake.*,
      SUM(calories) OVER(ORDER BY hours) AS running_total
FROM calorie_intake;

Let's go through a few more scenarios in which running totals are used. Keep in mind that the window functions that calculate running totals stay mostly the same. So, from now on, I will briefly explain the examples without including the SQL code.

5.   Confirmed COVID-19 Cases

Each country collects information about the total number of confirmed COVID-19 cases, deaths, and cured cases each day. With this new information, running totals are updated so that each country knows how many confirmed cases, deaths, and cured cases it has had so far, from the start of the pandemic until now.

For more details, check out how the running total is calculated using John Hopkins data: how to analyze COVID-19 data.

6.   Registered Users of a Mobile Application

Owners of mobile applications usually want to see the cumulative sum of new registered users and installs/uninstalls that were made in the last month, quarter, and/or year. Here again, you can use running totals to get better insights. These statistics tell the owners how the application or a specific feature has been received and how to develop the product in the future.

Similarly, if you run a website, one of the most important metrics to track is the total number of page views or visits. This information is usually gathered daily. By calculating the running total, you can see how the cumulative sum behaves over time and how it compares with past running totals.

7.   Airline Loyalty Programs

This concept is probably well-known to those who fly frequently. With each flight, you collect points. Once you collect a certain number of points, you get discounts and/or rewards.

A running total is used to calculate the total number of points that you have in your account. Every time you purchase a ticket, the running total is refreshed, and your points increase.

Summary

In this article, I showed how running totals are used in different scenarios. As you have learned, running totals have many applications.

SQL window functions enable you to calculate running totals relatively easily with only a few lines of code. Window functions are used when dealing with complex calculations. Once you learn the syntax, you will write cleaner and more understandable code.

Although I only explained how to use window functions for running total calculations, window functions are widely used for a variety of calculations. In this article with window function examples, you can learn about other ways to use window functions.

We also offer a great interactive Window Functions course with a lot of examples. If you want to dive deep into window functions, I highly recommend taking this course. It provides plenty of exercises for practice, which is important when acquiring new SQL skills.