CODEWITHSUNDEEP
sqlsql-serversql-server-2008
GPH
GPH

Reputation: 1897

aggregating data to getting running total

I have a query which outputs the below

enter image description here

I need to get it to provide a running total so for March it would give whats been paid in Feb and Mar, then for April Feb,Mar & Apr and so on.

Never come across needing this kind of aggregation before in SQL.

Upvotes: 1

Views: 75

Answers (4)

roman
roman

Reputation: 117345

You can check this question and my answer on it. Turns out that recursive common table expression is the fastest method to get running total in SQL Server < 2012.

So in your case it could be something like:

with cte as
(
    select T.MonthID, T.Month, T.Paid, T.Paid as Running_Paid
    from Table1 as T
    where T.MonthID = 118
    union all
    select T.MonthID, T.Month, T.Paid, T.Paid + C.Running_Paid as Running_Paid
    from cte as C
        inner join Table1 as T on T.MonthID = C.MonthID + 1
)
select *
from cte
option (maxrecursion 0)

Upvotes: 1

M.Ali
M.Ali

Reputation: 69504

SELECT
     T.MonthId
    ,T.[Month]
    ,T.Value
    ,RT.runningTotal
from Table_Name T
             CROSS APPLY 
                        (
                          SELECT SUM(value) as runningTotal
                          FROM Table_Name
                          WHERE MonthId <= T.MonthId
                        ) as RT
order by T.MonthId

Test Data

declare @t1 TABLE (Monthid int, month varchar(10), Value decimal(18,2))

insert into @t1
values 
 (1,'JAN-13',35.00)
,(2, 'FEB-13',35.00) 
,(3,'MAR-13',35.00)
,(4,'APR-13',35.00)
,(5,'JUN-13',35.00)
,(6,'Jul-13',35.00)
,(7,'Aug-13',35.00)

SELECT
     T.MonthId
    ,T.[Month]
    ,T.Value
    ,RT.runningTotal
from @t1 T
             CROSS APPLY 
                        (
                          SELECT SUM(value) as runningTotal
                          FROM @t1
                          WHERE MonthId <= T.MonthId
                        ) as RT
order by T.MonthId

RESULTS

MonthId Month   Value   runningTotal
1       JAN-13  35.00   35.00
2       FEB-13  35.00   70.00
3       MAR-13  35.00   105.00
4       APR-13  35.00   140.00
5       JUN-13  35.00   175.00
6       Jul-13  35.00   210.00
7       Aug-13  35.00   245.00

Upvotes: 1

Andrew
Andrew

Reputation: 8703

Running totals in 2008 are kind of a pain. SQL Fiddle seems to have gone MIA again, but here's a simplistic example of how you can do it.

declare @t1 TABLE (monthid int, mth varchar(10), paid decimal(18,2), running_paid decimal(18,2))

insert into @t1
values (1,'JAN-13',35.00,0)
,(2, 'FEB-13',35.00,0) 
,(3,'MAR-13',35.00,0)


declare @running decimal(18,2)
set @running= 0
update @t1 set running_paid = @running, @running= @running+ paid 

select
*
from
@t1

Which will give you:

ID  MTH     PAID    RUNNING_PAID
1   JAN-13  35.00   35.00
2   FEB-13  35.00   70.00
3   MAR-13  35.00   105.00

EDIT: As Bogdan Sahlean points out, this is a very funky little process. You could also use a cursor:

declare @t1 TABLE
(monthid int,
mth  varchar(10),
paid decimal(18,2)
)

insert into @t1
values (1,'JAN-13',35.00)
,(2, 'FEB-13',35.00) 
,(3,'MAR-13',35.00)


declare @running table
(monthid int,
mth  varchar(10),
paid decimal(18,2),
running_paid decimal(18,2))


declare c cursor
for select monthid,mth,paid from @t1
open c
declare @Id int
declare @Mth varchar(10)
declare @paid decimal(18,2)
declare @Running_Total decimal(18,2)
set @Running_Total = 0
fetch next from c
    into @Id,@Mth,@paid

WHILE @@FETCH_STATUS = 0
    begin
        fetch next from c
            into @Id,@Mth,@paid
        select @Running_Total = @Running_Total + @paid  --Here's this version's hack for running total
        insert into @running values (@Id,@Mth,@paid,@Running_Total) 
    end

select
*
from
@running

They all kind of stink. This is a lot easier in SQL 2012.

Upvotes: 0

T I
T I

Reputation: 9933

select 
  [monthid], 
  [month], 
  ( select sum([paid]) from tbl t2 where t2.[monthid] <= t1.[monthid] ) as paid
from tbl t1

Upvotes: 1

Related Questions