How to add consecutive row values

Question

How to add consecutive row values

gundan01

Old Hand

Points: 361
More actions
April 7, 2010 at 9:37 am

#386656

I'm trying to get sum of consecutive value in a table as a Total column.
Example Data:
table A
Date Value Item
10/10/2009 10 Item1
10/25/2009 25 Item1
09/10/2009 10 Item1
05/15/2010 30 Item1
04/10/2009 5 Item1
10/30/2010 10 Item2
05/15/2010 30 Item2
04/10/2009 5 Item2
10/30/2010 10 Item2
Result
Date Value Item Total
04/10/2009 5 Item1 5
09/10/2009 10 Item1 15
10/10/2009 10 Item1 25
10/25/2009 25 Item1 50
05/15/2010 30 Item1 80
The result will be total equal to value column for the first row, Total for second row = Total first + second value
the third will be Total from second + third value and so on.
Can anyone help me out to get this done. One more thing is that the adding is based on item column.
Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

You know, the people that help out here are all un-paid volunteers. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.

Now, that being said, here is what I think you want to see. This utilizes a method that has very specific rules for how to do it, so be sure to read the article referenced in the code.

-- See how this starts off by creating a table

-- and inserting representative test data into it?

-- If you do this, it makes it a LOT easier for all

-- of the volunteers on this site to just copy/paste

-- this into a query window and start working on it.

if object_id('tempdb..#test') IS NOT NULL DROP TABLE #test

CREATE TABLE #test (

[Date] datetime,

Value int,

Item varchar(10),

RunningTotal int)

INSERT INTO #test ([Date], Value, Item)

SELECT '10/10/2009', 10, 'Item1' UNION ALL

SELECT '10/25/2009', 25, 'Item1' UNION ALL

SELECT '09/10/2009', 10, 'Item1' UNION ALL

SELECT '05/15/2010', 30, 'Item1' UNION ALL

SELECT '04/10/2009', 5, 'Item1' UNION ALL

SELECT '10/30/2010', 10, 'Item2' UNION ALL

SELECT '05/15/2010', 30, 'Item2' UNION ALL

SELECT '04/10/2009', 5, 'Item2' UNION ALL

SELECT '10/30/2010', 10, 'Item2'

-- declare and set a few variables for use in the update statement

DECLARE @RunningTotal int,

@LastItem varchar(10)

set @RunningTotal = 0

set @LastItem = ''

-- create a clustered index on the column needed for

-- the proper order to calculate the running total.

CREATE CLUSTERED INDEX #testIDX ON #test (Item, [Date])

-- This form of the UPDATE statement has some rules for proper usage.

-- See Jeff Modem's article at http://www.sqlservercentral.com/articles/T-SQL/68467/

-- for a complete discussion of how this works, and all of the rules for utilizing it.

UPDATE t

SET @RunningTotal = case when Item = @LastItem then @RunningTotal + Value

else Value

end,

RunningTotal = @RunningTotal,

@LastItem = Item -- << Anchor column, also used for above case statement.

FROM #test t WITH (TABLOCKX) -- << use the TABLOCKX hint

OPTION (MAXDOP 1) -- << Prevent parallelism

-- show the data

SELECT * FROM #test order by Item, [Date]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

gundan01 Old Hand Points: 361 More actions · Answer 2

Thanks for the recommendation and for the response. That was what I was looking for. I really appreciate you help and the time you spent solving the problem.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 3

For fun, here is another way:SELECT T.[Date], T.Value, T.Item, TT.Total

FROM @Test AS T

CROSS APPLY (SELECT SUM(Value) AS Total FROM @Test WHERE Item = T.Item AND [Date] <= T.[Date]) AS TT

WHERE T.Item = 'Item1'

ORDER BY T.[Date]

gundan01 Old Hand Points: 361 More actions · Answer 4

Ahhhh!!! This actually is a good because I tried to used the other post with reporting services and I actually had to create a store procedure to use it but with this one I think I can play around without having to create store procedure. Thanks this was really useful.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

gundan01 (4/7/2010)
Ahhhh!!! This actually is a good because I tried to used the other post with reporting services and I actually had to create a store procedure to use it but with this one I think I can play around without having to create store procedure. Thanks this was really useful.

Actually, (and no offense to the person who posted it), it can be very, very bad. Please click the following link for a full explanation as to why.

Hidden RBAR: Triangular Joins <<---Click here[/url]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)