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
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.
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
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.