SQL > Advanced SQL > Running Totals

Displaying running totals is a common request, and there is no straightforward way to do so in SQL. The idea for using SQL to display running totals similar to that for displaying rank: first do a self-join, then, list out the results in order. 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

NameSales
John10
Jennifer15
Stella20
Sophia40
Greg50
Jeff20

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:

NameSalesRunning_Total
Greg5050
Sophia4090
Stella20110
Jeff20130
Jennifer15145
John10155

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

Next: SQL Percent To Total




Copyright © 2014 1keydata.com   All Rights Reserved.   Privacy Policy