Home > SQL Tips > Calculating Running Totals

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
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:

About these ads
Categories: SQL Tips Tags:
  1. James E. Espitia C.
    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 4, 2011 at 10:23 am
  2. April 13, 2012 at 1:38 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 389 other followers

%d bloggers like this: