How can I get the cumulative sum of a field in a table

For Eg.

I have a table like gias given below:

Name Amount

aaa 10

bbb 20

and I want an output like one given below on running SQL query or stored procedure

Name Amount cumulative amount

aaa 10 10

bbb 20 30

can anyone plz help me on this

As you presented the problem the solution is :

SELECT Name, Amount,

CASE Name

WHEN 'bbb' THEN (select sum(amount) from dbo.cum)

else amount

END AS 'cumulative amount'

FROM dbo.cum

Output :

Name Amount cumulative amount

- -- --

aaa 10 10

bbb 20 30

(2 row(s) affected)

I don't understand the reason of sum by 'bbb' and not 'aaa'.

|||

this will do:

Code Snippet

select t1.*, (select sum(amount) from tb t2 where t2.name<=t1.name) [cumulative amount]

from tb t1

order by t1.name

|||Actually the table that I posted was just a sample one.Actual table holds thousands of records as below:

Table Name:LedgerTrans

Accountnum TransDate Voucher Amount

100613 2007-05-29 10000037_056 5000

100613 2007-01-01 10000022_056 145

120080 2007-01-01 000001_BWO -15

120080 2007-01-31 10000027_056 200

120080 2007-01-05 10000025_058 -40

523456 2007-03-05 10000034_056 -10

678590 2007-01-25 10000039_055 50

And the output should be like:

Accountnum TransDate Voucher Amount CumulativeAmount

100613 2007-05-29 10000037_056 5000 5000

100613 2007-01-01 10000022_056 145 5145

120080 2007-01-01 000001_BWO -15 4130

120080 2007-01-31 10000027_056 200 4330

120080 2007-01-05 10000025_058 -40 4290

523456 2007-03-05 10000034_056 -10 4280

678590 2007-01-25 10000039_055 50 4330

i.e. cumulativeAmount field value should be calculated by summing up amount values.

So I think "case" would not work for a table with thousands of records.

Is there any other way to achieve it.

|||Actually the table that I posted was just a sample one.Actual table holds thousands of records as below:

Table Name:LedgerTrans

Accountnum TransDate Voucher Amount

100613 2007-05-29 10000037_056 5000

100613 2007-01-01 10000022_056 145

120080 2007-01-01 000001_BWO -15

120080 2007-01-31 10000027_056 200

120080 2007-01-05 10000025_058 -40

523456 2007-03-05 10000034_056 -10

678590 2007-01-25 10000039_055 50

And the output should be like:

Accountnum TransDate Voucher Amount CumulativeAmount

100613 2007-05-29 10000037_056 5000 5000

100613 2007-01-01 10000022_056 145 5145

120080 2007-01-01 000001_BWO -15 4130

120080 2007-01-31 10000027_056 200 4330

120080 2007-01-05 10000025_058 -40 4290

523456 2007-03-05 10000034_056 -10 4280

678590 2007-01-25 10000039_055 50 4330

i.e. cumulativeAmount field value should be calculated by summing up amount values.

So I think the above code snippet would not work for a table with thousands of records.

Is there any other way to achieve it.

|||

Why don't you use ROLLUP operator ?

As we see in Books Online :

the table

Item Color Quantity -- -- -- Table Blue 124 Table Red 223 Chair Blue 101 Chair Red 210

can be transformed in :Item Color QtySum - -- -- Chair Blue 101.00 Chair Red 210.00 Chair ALL 311.00 -- this is the sum Table Blue 124.00 Table Red 223.00 Table ALL 347.00 -- this is the sum ALL ALL 658.00 -- this the comple sumIs more elegant!

|||

I don't think i understand the cumulativeAmount column. Are you records in any particular order because if they are not, then the cumulative amount column would vary depending on how the records are returned.

I think OJ's suggestion works best and you could just modify it slightly as follows:

Code Snippet

select t1.*, (select sum(amount) from LedgerTrans t2 where t2.TransDate<=t1.TransDate) [cumulative amount]
from LedgerTrans t1
order by t1.TransDate

This will give you all transactions in order they were made and a running cumulative total in the final column and would work for any number of rows.

HTH!

|||

Hi,

Sorry but my requirement is as stated above.

I'm not good at T-Sql as I have just began to learn it.

But I think we have to use cursor concept to accomplish the task stated by me.

I have no idea how to apply cursors.If u have any idea abt it plz help me out.

|||

I had already tried it out.Thats giving wrong output.

Do u have any idea as to how we can achieve it using cursor concept

|||

So do you require the output of your data to be in any particular order? Or do you just want to have a cumulative amount column which "could" be different every time you execute the query?

ie

transdate, amount, cumulative

1/1/2007, 20, 20

1/3/2007, 10, 30

1/4/2007, 40, 70

transdate, amount, cumulative

1/3/2007, 10, 10

1/4/2007, 40, 50

1/1/2007, 20, 70

Both of these results are using the same data but as there is no specified order, the cumulative column is different. To get a consistent results set you could add an order on transdate. If you don't put an order on the query, then you are putting your faith in the database engine to produce the correct results which, it "might" but there are no guarentees.

You can use a CURSOR if you really feel like you need to (check Books Online for syntax) but, unless i'm missing something here, i don't think its necessary.

HTH!

|||

try this code made on this table :

USE [test]
GO

CREATE TABLE [dbo].[cumtest](
[id] [int] IDENTITY(1,1) NOT NULL,
[t] [int] NULL,
[cum] [int] NULL
) ON [secondary]

declare @.t int

declare @.id int

declare @.cum int

DECLARE AccCursor CURSOR

FOR SELECT id,t FROM cumtest

OPEN AccCursor

SET @.cum =0

FETCH NEXT FROM AccCursor INTO @.id,@.t

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.cum = @.cum + @.t

update cumtest set cum=@.cum where id=@.id

FETCH NEXT FROM AccCursor INTO @.id,@.t

END

CLOSE AccCursor

DEALLOCATE AccCursor

Output is :

id t cum

-- -- --

1 5000 5000

2 145 5145

3 -15 5130

4 200 5330

5 -40 5290

6 -10 5280

7 50 5330

(7 row(s) affected)

|||

No order doesn't matter for me.The output in any order is accepable.

The matter is with output.The query is not summing up the amounts right.

|||

How is the summing incorrect? The query seemed to work for me, though i don't have access to your data of course. I did notice that your example output had a strange inconsistency with the rows 5145 and 4130 - though i guess this was just a typo.

In the meantime, ggciubuc has kindly provided a cursor solution (also using SUM) which i'd be interested to know if it solved your issue.

Good luck!

|||

Sure, the table cumtest can be joined with your table and presented in a query (see 1 post above)

|||

Hi,

I dnt know your name but thanks a lot for your help.

This code snippet really worked.

Thanks & Regards,

Deepika