running totals

Question

running totals

_ms65g_

SSCommitted

Points: 1504
More actions
June 8, 2010 at 7:04 am

#387097

Hi,
I want to calculate total points of current row with previous rows by using a query.
My table schema is similar to follow; and I know two methods but I am looking for a better approach like using Windowed Functions
Please suggest me a nice solution.
declare @t table (recID int identity, point int)
insert @t select 10 union select 25 union select 60
--solution#1
SELECT recID, Point, SumPiont = (SELECT SUM(Point)
FROM @t AS T1
WHERE T1.recID <= T2.recID)
FROM @t AS T2;
--Solution#2
SELECT T1.recID, T1.point, SUM(T2.point) AS Sumpoint
FROM @t AS T1
INNER JOIN @t T2
ON T1.recID >= T2.recID
GROUP BY T1.recID, T1.point
/*
recID Point SumPiont
----------- ----------- -----------
1 10 10
2 25 35
3 60 95
*/

Viewing 3 posts - 1 through 2 (of 2 total)

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

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 1

I would suggest this article[/url] by Jeff Moden. It is one of the best solutions I've seen.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Thanks for the plug, Stefan. :blush:

--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)
Intro to Tally Tables and Functions