Get sum of all previous values? - Total so far?

Question

Possible Duplicate:
How do I calculate a running total in SQL without using a cursor?

It's a little difficult to explain, so I'll show what I want with an example:

Lets say we have the following table named MonthProfit:

[MonthId][Profit]
1, 10 -- January
2, 20 -- February
3, 30
4, 40
5, 50
6, 60
7, 70
8, 80
9, 90
10, 100
11, 110
12, 120 -- December

Column profit represents the profit for that month.

However, if we have 10 profit in January, and 20 in February, in February we have a total profit of 30.

so I'd like to create a view that shows the following:

[MonthId][Profit][ProfitTotal]
1, 10, 10 -- January
2, 20, 30 -- February
3, 30, 60
4, 40, 100
5, 50, 150
6, 60, 210
7, 70, 280
8, 80, 360
9, 90, 450
10, 100, 550
11, 110, 660
12, 120, 780 -- December

What I did now to solve it, is a view like this:

SELECT [MonthId]
       ,[Profit]
       , (SELECT SUM([Profit])
         FROM MonthProfit
         WHERE [MonthId] <= outer.[MonthId]) as ProfitTotal
FROM MonthProfit as outer

However, I assume this is pretty slow because it has to recount everything all the time, and it does not seem very elegant to me. Is there a "good" way to do this?

also http://stackoverflow.com/questions/814054/complicated-sql-query-for-a-running-total-column — JNK, Nov 21 '11 at 14:12
Just search for "Running total SQL" and you will get many hits on this. — JNK, Nov 21 '11 at 14:13

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

I have tried a small example here for your reference this generates the results as per the requirements

CREATE TABLE [dbo].[tbl_TotalPrevious](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[values] [bigint] NOT NULL)

INSERT DATA INTO THE TABLE

insert into tbl_TotalPrevious values ('A', 10)
insert into tbl_TotalPrevious values ('B', 20)
insert into tbl_TotalPrevious values ('C', 10)
insert into tbl_TotalPrevious values ('D', 10)
insert into tbl_TotalPrevious values ('E', 10)
insert into tbl_TotalPrevious values ('F', 10)
insert into tbl_TotalPrevious values ('G', 10)
insert into tbl_TotalPrevious values ('H', 10)
insert into tbl_TotalPrevious values ('I', 10)
insert into tbl_TotalPrevious values ('J', 10)
insert into tbl_TotalPrevious values ('K', 10)
insert into tbl_TotalPrevious values ('L', 10)
insert into tbl_TotalPrevious values ('M', 10)
insert into tbl_TotalPrevious values ('N', 10)
insert into tbl_TotalPrevious values ('O', 10)
insert into tbl_TotalPrevious values ('P', 10)
insert into tbl_TotalPrevious values ('Q', 10)
insert into tbl_TotalPrevious values ('R', 10)
insert into tbl_TotalPrevious values ('S', 10)
insert into tbl_TotalPrevious values ('T', 10)
insert into tbl_TotalPrevious values ('U', 10)
insert into tbl_TotalPrevious values ('V', 10)
insert into tbl_TotalPrevious values ('W', 10)
insert into tbl_TotalPrevious values ('X', 10)
insert into tbl_TotalPrevious values ('Y', 10)

Create a Function eg.

ALTER FUNCTION testtotal 
(
    @id int
)
RETURNS int
AS
BEGIN
    DECLARE @Result int
    SELECT @Result = (SELECT SUM([values])
         FROM tbl_TotalPrevious
         WHERE [id] <= @id)

    RETURN @Result

END
GO

RESULTS GENERATED FROM A SINGLE QUERY

SELECT [id],[values], (dbo.testtotal(id)) as TotalVals FROM tbl_TotalPrevious

HOPE THE ABOVE SOLVES YOUR PURPOSE WITH THE TIMING ISSUE AND GENERATES THE DATA FASTER AS REQUIRED.

RESULTS IMAGE

Doesnt that do exactly the same as my subquery? Only encapsulated into a function? — Ron Sijm, Nov 21 '11 at 14:34
But doing this will still decrease the time and table scan due to a function. but i think u should implement and test you execution plan for live data you have. — Murtaza, Nov 21 '11 at 14:39

score 0 · Answer 2 · answered Nov 21 '11 at 14:26

Try something like this below, at first glance looks fine :-).

create table #tab ([MonthId] int, [Profit] int)

insert into #tab select 1, 10 -- January
insert into #tab select 2, 20 -- February
insert into #tab select 3, 30
insert into #tab select 4, 40
insert into #tab select 5, 50
insert into #tab select 6, 60
insert into #tab select 7, 70
insert into #tab select 8, 80
insert into #tab select 9, 90
insert into #tab select 10, 100
insert into #tab select 11, 110
insert into #tab select 12, 120 -- December

select t.*, t3.total
from #tab t
join (
    select t1.monthId, 
        sum(t2.profit) as total
    from #tab t1
    join #tab t2 on t1.monthId >= t2.monthId
    group by t1.monthId
) t3 on t.monthId = t3.monthId

score 0 · Answer 3 · answered Nov 21 '11 at 14:35

declare @MonthProfit table
(
  [MonthId] int,
  [Profit] int
)

insert into @MonthProfit values
(1, 10),(2, 20),(3, 30),(4, 40),
(5, 50),(6, 60),(7, 70),(8, 80),
(9, 90),(10, 100),(11, 110),(12, 120)

;with C as
(
  select M.MonthId,
         M.Profit
  from @MonthProfit as M
  where M.MonthId = 1
  union all
  select M.MonthId,
         C.Profit + M.Profit
  from @MonthProfit as M
    inner join C 
      on M.MonthId = C.MonthId + 1
)
select C.MonthId,
       C.Profit 
from C
order by C.MonthId

score -1 · Answer 4 · answered Nov 21 '11 at 14:19

That seems to be a not very elegant option but it works properly.

If you want to improve, you have several choices:

make a third column in the table (the ProfitTotal) that will be updated (trigger) on insert/update values or if you want make it on select;
Create index to make it faster;
Update the table statistics.

Get sum of all previous values? - Total so far?

4 Answers4