cumulative running total in a query?

Question

cumulative running total in a query?

pop

SSCrazy

Points: 2130
More actions
July 24, 2005 at 9:24 pm

#89209

Assuming I have a table with the just the fields "MemberNo" and "ClaimCount", for example "ABC123" "2", where there are multiple records having the same MemberNo with a different ClaimCount, how could I create a third column, say, "Running Total" in a Query, which would keep a cumulative running count of the ClaimCount value for each MemberNo.?
Example:
MemberNo ClaimCount RunningTot
'ABC123' 1 1
'ABC123' 2 3
'ABC123' 1 4
Thanks.

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

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

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

You need to be able to sort the rows in your table in some way such that you can tell where to begin and where to end your calculation (in other words, referring to your example, which row should have a RunningTot of 4?). Do you have a unique id on your table?

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

Try this:

if exists (select * from dbo.sysobjects

where id = object_id(N'Test')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table Test

GO

CREATE TABLE Test (MemberNo varchar(6)

, ClaimCount int

, RunningTot int)

GO

INSERT INTO Test (MemberNo,ClaimCount)

VALUES ('ABC123',1)

INSERT INTO Test (MemberNo,ClaimCount)

VALUES ('ABC123',2)

INSERT INTO Test (MemberNo,ClaimCount)

VALUES ('ABC123',1)

GO

DECLARE @RunningTot int

SET @RunningTot = 0

UPDATE Test

SET @RunningTot = RunningTot = @RunningTot + ClaimCount

GO

SELECT * FROM Test

GO

Andy

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 3

Very nice.... learned a new trick there (column RunningTot should be nullable in order for your example to run).

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

A well known trick is this: http://www.sqlteam.com/item.asp?ItemID=765

The major drawback here is that you can't control the order in which the rows are updated. There is no guarantee that they are updated in the order you want them to.

Anyway, you should really change the underlying table structure and add at least a PRIMARY KEY. The way your structure looks like now, the data is kinda meaningless.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 5

Thanks, Frank... Although this is a very nice trick that might be useful in some situations, I agree with your remarks.