SQL > Advanced SQL > Running Totals

There are multiple ways to calculate running totals in SQL. We will show two ways here: The first without using the window function, the second using the window function.

Not using window function

Calculating the running total without the window function requires us to first do a self-join, then list out the results in order. This is similar to finding a rank without using the window function. Where as finding the rank requires doing a count on the number of records that's listed ahead of (and including) the record of interest, finding the running total requires summing the values for the records that's listed ahead of (and including) the record of interest.

Let's use an example to illustrate. Say we have the following table,

Table Total_Sales

 Name  Sales 
 John  10 
 Jennifer  15 
 Stella  20 
 Sophia  40 
 Greg  50 
 Jeff  20 

we would type,

SELECT a1.Name, a1.Sales, SUM(a2.Sales) Running_Total
FROM Total_Sales a1, Total_Sales a2
WHERE a1.Sales <= a2.sales or (a1.Sales=a2.Sales and a1.Name = a2.Name)
GROUP BY a1.Name, a1.Sales
ORDER BY a1.Sales DESC, a1.Name DESC;

Result:

Name Sales Running_Total
Greg 5050
Sophia 4090
Stella 20110
Jeff 20130
Jennifer 15145
John 10155

The combination of the WHERE clause and the ORDER BY clause ensure that the proper running totals are tabulated when there are duplicate values.

Using window function

We will use the SUM() function as part of the window function as follows:

SELECT a1.Name, a1.Sales, SUM(a2.Sales) OVER (ORDER BY Sales DESC) RT
FROM Total_Sales
ORDER BY Sales DESC;

Result:

Name Sales RT
Greg 5050
Sophia 4090
Stella 20110
Jeff 20130
Jennifer 15145
John 10155

The SUM() function causes the window function to caluclate the sum of all the rows from the first row up to the current row. So, for Sophia, the running total column of 90 is the result of adding 50 and 40.

Notice the code involving the window function is much simpler and the logic is less complicated. Hence using the window function is seen as the preferred way to calculate running totals.

List of SQL Complex Operations

 Operation Description
 Rank  Calculates the ranking of a series of numbers. 
 Median  Calculates the median of a series of numbers. 
 Running Totals  Calculates the running total for a series of numbers. 
 Percent To Total  Calculates the percent to total for each number in a series. 
 Cumulative Percent To Total  Calculates the cumulative percent to total for each number in a series. 


Next: SQL Percent To Total

This page was last updated on June 19, 2022.




Copyright © 2022   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact