question

Rad avatar image
Rad asked

Help with this query please (running total)

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

sql-server-2008t-sql
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Is the ordering of the rows based on an alphabetical ordering of the names?
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

NYSystemsAnalyst avatar image
NYSystemsAnalyst answered

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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mark avatar image
Mark answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.