A table has name and scores.
Name Scores A 10 B 20 C 10
Output should be
Name Scores A 10 B 30 C 40
i.e the scores should be added to previous rows' score. How do I write a query for this?
Thanks in advance
Answer by Matt Whitfield ·
There are several excellent examples of how you can efficiently solve this problem in the solutions to Speed Phreak Competition 1. You would be well advised to have a look there, and see what methods you might like to use to generate running total efficiently over a large number of rows.
Answer by Mark ·
This is one area where I wish that there was a pre-built function. Running totals are frustrating since they're simple in concept, but not easy to calculate.
Also, the comments here refer to the dreaded RBAR. But if you want a running total, SQL Sderver has to total each value RBAR anyway doesn't it? I don't see a way around that even if the results are grouped.
Answer by NYSystemsAnalyst ·
I borrowed this from http://www.sqlteam.com/article/calculating-running-totals
SELECT sc1.Name AS [Name], sum(sc2.Scores) AS [Scores] FROM ScoreTable AS sc1 CROSS JOIN ScoreTable AS sc2 WHERE (sc2.name <= sc1.name) GROUP BY sc1.name ORDER BY sc2.name