Running Total in Select Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Running Total in Select Query

Author

Topic

nns80
Starting Member

9 Posts

Posted - 2004-05-18 : 20:34:48

I want to get the running total of an amount field that I calculate on the fly in a select query.

Example:

DECLARE @RunningTotal MONEY
SET @RunningTotal = 0

SELECT
Count,
(Amount * Count) NewAmt,
(@RunningTotal + Amount * Count * .01) AS RunningTotal
FROM TableA

Sample Output:

Count NewAmt RunningTotal
1 100 1.00
3 200 7.00
3 300 16.00
2 400 24.00

Another thing, I do not have an id on this table based on which I can sort and get all the prior total as suggested in the solution at http://www.sqlteam.com/item.asp?ItemID=3856.

Please help me with the query.

Thanks,

-NS.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-18 : 22:10:53

Didn't you post this on another forum. It looks familiar.

DECLARE @totals TABLE(
Code CHAR(4),
Amount MONEY,
Year INT)

INSERT @totals(
Code,
Amount,
Year)

SELECT 'AAAA',100000,'1998' UNION ALL
SELECT 'AAAA',200000,'1999' UNION ALL
SELECT 'AAAA',300000,'2000' UNION ALL
SELECT 'BBBB',100000,'1998' UNION ALL
SELECT 'BBBB',100000,'2000' UNION ALL
SELECT 'CCCC',100000,'1999'

SELECT
t.Code,
t.Amount,
t.Year,
(SELECT SUM(Amount) FROM @totals WHERE Year <= t.Year AND Code = t.Code) AS RtotalField
FROM
@totals t
GROUP BY
t.Code,
t.Amount,
t.Year

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nns80
Starting Member

9 Posts

Posted - 2004-05-18 : 22:42:56

yeah I did. However, your solution doesnt help me since I do not have a way to sort/identify my query based on the 'year' field that you show in the example. similar examples and ways to solve it are there in the link that I mentioned in my previous post.

However, in my situation I do not have such a value.

Thanks,
NS.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-18 : 23:00:54

--Setup:
DECLARE @running_total TABLE(
Count INT,
Amount INT)

INSERT @running_total(Count, Amount)
SELECT 1, 100
UNION ALL
SELECT 3, 100
UNION ALL
SELECT 3, 100
UNION ALL
SELECT 2, 100

--**Run the Query

--Load into a temp table with identity column:
DECLARE @running_total2 TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
Count INT,
Amount INT,
RunningTotal INT)

INSERT @running_total2(Count, Amount, RunningTotal)
SELECT Count, Amount, Count*Amount AS RunningTotal FROM @running_total

SELECT * FROM @running_total2

SELECT
rt.Count,
rt.RunningTotal NewAmt,
((SELECT SUM(RunningTotal) FROM @running_total2 WHERE ident <= rt.ident)*.01) AS RunningTotal
FROM
@running_total2 rt

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources