Running Total in Select Query

Question

Post reply

Running Total in Select Query

nns80

Mr or Mrs. 500

Points: 524
More actions
May 18, 2004 at 5:52 pm

#108205

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:
TableAId     NewAmt       RunningTotal
1               100             1.00
2               200             5.00
3               300             14.00
4               400             30.00
Please help me with the query.
Thanks,
-NS.

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

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

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 1

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

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

nns80 Mr or Mrs. 500 Points: 524 More actions · Answer 2

I do not have any sequential value as the 'year' in the above example and hence it can't be sorted on a field to get the sum of all previous values till that record.

-NS.

David.Poole SSC Guru Points: 76000 More actions · Answer 3

Presumably you have an ordering sequence?

Without details of your table structure the only way that I can think of doing it is by using a cursor, however I would tend to do running totals in a front end app, rather than within the stored procedure.

LinkedIn Profile

Dan Hare SSC-Addicted Points: 464 More actions · Answer 4

in the temp table, define with an identity id field.

insert into the other fields, then use the identity to sort on.

dan

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 5

You dont have to have an ordering sequence per se. It is enough to have a PK or Unique Constraint on the table to order by.

In your example it seems to me that field TableAId would serve this purpose.

And You can use the method proposed by Derrick Leggett and order by PK or UC in the table.

/rockmoose

You must unlearn what You have learnt

nns80 Mr or Mrs. 500 Points: 524 More actions · Answer 6

I defined a temp table and got it working.

Thanks for all your help.

-NS.