Home > SQL Tips > SQL Query for calculating Running Totals

## SQL Query for calculating Running Totals

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
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
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:
1. April 30, 2014 at 9:43 am

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.

1. July 7, 2015 at 2:39 pm
2. April 13, 2012 at 1:38 pm
3. July 4, 2011 at 10:23 am