A table has name and scores.
Ex:
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
asked
Help with this query please (running total)
Is the ordering of the rows based on an alphabetical ordering of the names?
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.
