SQL Query for calculating Running Totals

Home > SQL Tips > SQL Query for calculating Running Totals

SQL Query for calculating Running Totals

July 4, 2011 Leave a comment Go to comments

A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence, simply by adding the value of the new number to the running total.

Let’s see how to get these Running totals by creating a simple SQL queries below:

USE [AdventureWorks]
GO

-- Method 1: (Query Cost 9%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  a.row, a.SalesPersonID, a.FirstName, a.LastName, a.OrderDate, a.TotalDue, sum(b.TotalDue) as RunTotal
from RunTot a
join RunTot b on a.SalesPersonID = b.SalesPersonID and a.row >= b.row
group by a.row, a.SalesPersonID, a.FirstName, a.LastName, a.TotalDue, a.OrderDate
order by a.SalesPersonID, a.row

-- Method 2: (Query Cost 91%)
;with RunTot as (
select row_number() over(partition by s.SalesPersonID order by s.OrderDate) as row,
s.SalesPersonID, p.FirstName, p.LastName, s.TotalDue, s.OrderDate
from Sales.SalesOrderHeader s
join Person.Contact p
on s.SalesPersonID = p.ContactID)
select  row, SalesPersonID, FirstName, LastName, OrderDate, TotalDue,
(select SUM(TotalDue) from RunTot b where b.SalesPersonID=a.SalesPersonID and b.row<=a.row) as RunTotal
from RunTot a

-- Output Listed below:

Categories: SQL Tips Tags: RUNNING TOTALS

Comments (1) Trackbacks (3) Leave a comment Trackback

James E. Espitia C.

April 30, 2014 at 9:43 am

Reply

Good Night!

I’m new with Recursive CTE but it’s difficult to me because it’s a new thinking way.

I’m try to understand a simple example i. e. a credit table like this:

1 A B C D E F
2 Fixed 1% Payments may change (B * C)
Amount * Percentage “(D-E)
Payment – Interest”
3 Amount Percentage Payment Interest Capital
4 Credit
Amount ->
Initial Point $ 1,000,000.00 1.00% $ 100,000.00 $ 10,000.00 $ 90,000.00
5 B3 – F3 -> $ 910,000.00 1.00% $ 9,100.00 ($ 9,100.00)
6 B4 – F4 -> $ 919,100.00 1.00% $ 100,000.00 $ 9,191.00 $ 90,809.00
7 B5 – F5 -> $ 828,291.00 1.00% $ 100,000.00 $ 8,282.91 $ 91,717.09
8 B6 – F6 -> $ 736,573.91 1.00% $ 100,000.00 $ 7,365.74 $ 92,634.26
9 B7 – F7 -> $ 643,939.65 1.00% $ 100,000.00 $ 6,439.40 $ 93,560.60
10 B8 – F8 -> $ 550,379.05 1.00% $ 100,000.00 $ 5,503.79 $ 94,496.21
11 B9 – F9 -> $ 455,882.84 1.00% $ 100,000.00 $ 4,558.83 $ 95,441.17
12 B10 – F10 -> $ 360,441.66 1.00% $ 100,000.00 $ 3,604.42 $ 96,395.58
13 B11 – F11 -> $ 264,046.08 1.00% $ 100,000.00 $ 2,640.46 $ 97,359.54
14 B12 – F12 -> $ 166,686.54 1.00% $ 100,000.00 $ 1,666.87 $ 98,333.13
15 B13 – F13 -> $ 68,353.41 1.00% $ 69,036.94 $ 683.53 $ 68,353.41
16 “B14 – F14
Final Point ->
Amount $0 $ 0.00 1.00% $ 0.00 $ 0.00

I hope this may be readable because I can’t attach an image.

A credit is an easy example because we begin with an initial value, we have a fee, an interest and the difference between the payment and interest goes as a payment to capital and it keeps going to zero.

May you help me please to understand how Recursive CTE works with this example.

Thanks a lot for your special help.