Running Total

[vbjohn]

Is there a way that you can have a running total?

For Example:

1
2
3
4
5
6


AND THEN These records would already be in the database.

Amount NewAmount
1 1
25 26
100 126
2 128
Any help would be great.

 

[chiph]

vbjohn-
You could do like you say, and keep the running total in the records themselves, but then you need a way to specify the sequence of the records. Remember, SQL doesn't care about order unless you tell it, so this is perfectly valid too: [tt]
2 128
1 1
100 126
25 26
[/tt]

I've been thinking that you could use a stored procedure to do this. Instead of making the SELECT statement against the table directly, you'd call the stored procedure which would calculate the running total for you. It'd do this by creating a temp table that looks just like the data table, but with the addition of your running-total column. You'd then insert-select into the temp table from the data table. And then you'd use a cursor to loop through the temp table, updating the running-total column as you go.

This technique has at least three advantages, and one big disadvantage. The advantages are that you don't have to store a calculated value in the database (considered to be a no-no), the query runs faster since it's pre-compiled, and the calls in your client programs become much simpler. The disadvantage is that reporting becomes more difficult.

Chip H.

 

[vbjohn]

It seems complicated. Never done an Stored Procedure before. If you can help me start it out then I can finish it.

 

[tlbroadbent]

Here is one possible solution. You can create a stored procedure or just run this query in Query Analyzer. I recommend creating a SP if this will be run more than a few times.

Create Procedure CreateRunningTotal AS

/* Just use code beyond this line if you don't want to create a procedure. */
Declare @tot int
Set @tot=0

/* Create temp table and store values from the table into it. */
Select Recid, Amount, 0 As TotAmount
Into #t
From table_name

/* The key statement follows. It adds Amount and the value of @tot, stores the new value in TotAmount and then stores TotAmount into the variable @tot. */
Update #t Set @tot=TotAmount=@tot+Amount

/* Select the results from the temp table */
Select * From #t

/* Drop the temp table */
Drop table #t

Let me know if you have questions. Terry L. Broadbent
Life would be easier if I had the source code. -Anonymous