Sum previus rows
-
I have at table like this:
col1 col2
------------
a 5
b 2
c 7
d 3
Can i make a sql sentence to get the following result:
col1 col2
------------
a 5
b 7
c 14
d 17
-
Yet another running total problem, follow this link that explains the 3 methods on how to calculate running totals.
For advanced method (i.e. Special Update method), see this article[/url] by Jeff Moden
--Ramesh
-
Ramesh (5/16/2009)
Yet another running total problem, follow this link that explains the 3 methods on how to calculate running totals.For advanced method (i.e. Special Update method), see this article[/url] by Jeff Moden
Thanks for the plug, Ramesh.
Whatever you do, don't use the "Celko Method" on that other site... it's hidden RBAR that will eat the face off your server in the face of any scalability. Read the following article as to why...
http://www.sqlservercentral.com/articles/T-SQL/61539/
And, my appologies on the "advanced method" link... I had them take down the article while I'm rewriting it because the "Order By" method was misleading. I'm getting closer to republishing the method but haven't completed some addition research I'm doing on it, yet.
Anyway, here's some code... the details of the workings are where they belong... in the comments.
[font="Courier New"]--===== Recreate the original sample data in a table.
-- This is NOT a part of the solution.
-- It just to setup some "proof" testign
CREATE TABLE #Sample
(
Col1 VARCHAR(5),
Col2 INT
)
INSERT INTO #Sample
SELECT 'a',5 UNION ALL
SELECT 'b',2 UNION ALL
SELECT 'c',7 UNION ALL
SELECT 'd',3
--===== First, if you can't update the original table,
-- copy the data into a temp table to work on it.
-- This is VERY fast and the myth of it causing
-- problems in TempDB is really a myth since
-- SQL Server 6.5 sp1.
SELECT ISNULL(Col1,'') AS Col1, --ISNULL makes a NOT NULL col
Col2
INTO #MyHead
FROM #Sample
--===== Updates on single tables do their work in order
-- by the clustered index. So, we'll make a clustered
-- index for the desired order of update. In this case,
-- we'll simply use a system named primary key
ALTER TABLE #MyHead
ADD PRIMARY KEY CLUSTERED (Col1) WITH FILLFACTOR = 100
--===== Declare a couple of variables just like you would if
-- you were doing the problem in VB or C...
-- Then do the "quirky" update. Yes, for data safety,
-- the anchor column and index hints are necessary.
DECLARE @Total INT,
@Anchor VARCHAR(5)
SELECT @Total = 0
UPDATE #MyHead
SET @Total = Col2 = Col2 + @Total,
@Anchor = Col1
FROM #MyHead WITH (INDEX(0),TABLOCKX)
--===== Display the final results.
SELECT *
FROM #MyHead
ORDER BY Col1
[/font]
--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)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply