Running Total & Count

Is there a way that I could keep a Running Total & a Running Count?

For Example:
Running Count.
Total of 8 Records..
1,2,3,4,5,6,7,8

Running Total.
1 - 1
10 - 11
20 - 31
100 - 131
vbjohnAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Here is a sample of a method using a cursor:

CREATE @cursor1 CURSOR FOR
SELECT Value FROM YourTable
DECLARE @Value INT, @RecordId INT, @RunningTotal INT
SET @RecordId = 0
SET @RunningTotal = 0
OPEN @cursor1

FETCH NEXT FROM @cursor1 INTO @value
WHILE @@FETCH_STATUS  = 0
    BEGIN
    SET @RecordId = @RecordId + 1
    SET @RunningTotal = @RunningTotal + @Value
    SELECT @RecordId, @Value, @RunningTotal
    END
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If the records have primary key like this:

RecordID  Value
1         1
2         10
3         20
4         100
aso

Then:

SELECT
  R.RecordID,
  R.Value,
  TunningTotal = (SELECT SUM(T.VALUE) FROM YourTable T WHERE T.RecordID <= R.RecordID )
FROM YourTable R

Cheers

0
 
Scott PletcherSenior DBACommented:
On a large table, you are probably better off using a CURSOR for table access and calculating the running count and running total with other code.  I know that CURSORs should generally be avoided but in a case like this I see no acceptable alternative.
0
 
vbjohnAuthor Commented:
So how would I do that?  With the Cursors.  I have no experience with Stored Procedures.  Any coding examples please let me know.
0
 
mironCommented:
there is a function, a really simple one, that I regularly forgot, and than re - create it again, when I have time, it is arithmetic progression. Right now I am between forgot and  re - create :).
If your increment is predictable, and you expect that rows will not be deleted some time in the future, performance and code will benefit of using this function instead of really summing up the values.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.