Back to articles list March 19, 2021 - 9 minutes read 7 Real-Life Situations When You Need a Running Total and How to Compute It in SQL Marija Ilic Marija works as a data scientist in the banking industry. She specializes in big data platforms (Cloudera and Hadoop) with software and technologies such as Hive/Impala, Python and PySpark, Kafka, and R. Marija has an extensive background in DWH/ETL development in the banking industry. Her main interests are predictive modeling, real-time decision-making, and social network analysis. Outside of work, Marija enjoys listening to her favorite LPs on her old gramophone—and never grows tired of its soothing crackle. Tags: sql learn sql running total 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: dateno_of_itemsrunning_total 2021-01-0110150 2021-01-021222 2021-01-031537 2021-01-04946 2021-01-052066 2021-01-061581 2021-01-071394 2021-01-0817111 2021-01-0921132 2021-01-1019151 2021-01-1116167 2021-01-1213180 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: dateplanplan_running_totalrealizationrealization_running_total 2021-01-3160606868 2021-02-287513544112 2021-03-31100235-- 2021-04-30100335-- 2021-05-31100435-- 2021-06-30100535-- 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: datetransactionbalance_amount 2020-12-0150005000 2020-12-03-504950 2020-12-04-1254825 2020-12-05-1854640 2020-12-06-1424498 2020-12-09-3504148 2020-12-10-5603588 2020-12-11-803508 2020-12-12-153493 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: productdatequantityprice(EUR)running_total bread2021-02-05 8:01:1011.21.2 milk2021-02-05 8:02:02112.2 apple2021-02-05 8:02:3022.54.7 icecream2021-02-05 8:03:01226.7 diapers2021-02-05 8:03:1011319.7 shampoo14- 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: hoursfoodquantitycaloriesrunning_total 8:30eggs2150150 8:30bread170220 8:30milk1105325 8:30butter140365 10:30banana1105470 11:30apple190560 13:30bread2150710 13:30meat13501060 13:30soup1801140 13:30salad1301170 16:00icecream12501420 17:00cake13201740 20:00sandwich13002040 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. Tags: sql learn sql running total You may also like What Is a SQL Running Total and How Do You Compute It? In SQL, the running total is a very common pattern. It’s frequently used in finance and data analysis. Find out what a SQL running total is and how to compute this cumulative sum with window functions. Read more When Do I Use SQL Window Functions? SQL window functions can help you quickly and accurately create useful reports and analyses. Learn more with real-world business examples. Read more SQL Window Functions vs. GROUP BY: What’s the Difference? Window functions and GROUP BY may seem similar at first, but they’re quite different. Learn how window functions differ from GROUP BY and aggregate functions. Read more What Is the Difference Between a GROUP BY and a PARTITION BY? What is the difference between a GROUP BY and a PARTITION BY in SQL queries? When should you use which? You can find the answers in today's article. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.