1

I have a table Grader:

+--------------------------+
|GraderName | Graded_Items |
+--------------------------+
|    A      |       21     |
|    B      |        7     |
|    C      |       17     |
|    D      |       91     |
|    E      |       84     |
|    F      |       52     |
|    G      |       34     |
+--------------------------+

And I want to get the SUM of the first and second row be placed in second row; SUM of first,second, and third row be placed in third row; and so on and forth to the other rows. See below:

+--------------------------------+
|GraderName | Graded_Items       |
+--------------------------------+
|    A      |21                  |
|    B      |21+7                |
|    C      |21+7+17             |
|    D      |21+7+17+91          |
|    E      |21+7+17+91+84       |
|    F      |21+7+17+91+84+52    |
|    G      |21+7+17+91+84+52+34 |
+--------------------------------+

I want to achieve this result:

+--------------------------+
|GraderName | Graded_Items |
+--------------------------+
|    A      |       21     |
|    B      |       28     |
|    C      |       45     |
|    D      |      136     |
|    E      |      220     |
|    F      |      272     |
|    G      |      306     |
+--------------------------+

I've tried this query:

SELECT Grader,Graded_Items FROM Grader WHERE Grader='A'
UNION ALL
SELECT MAX(Grader),SUM(Graded_Items) FROM Grader WHERE Grader in ('A','B')
UNION ALL
SELECT MAX(Grader),SUM(Graded_Items) FROM Grader WHERE Grader in ('A','B','C')
UNION ALL
SELECT MAX(Grader),SUM(Graded_Items) FROM Grader WHERE Grader in ('A','B','C','D')
UNION ALL
SELECT MAX(Grader),SUM(Graded_Items) FROM Grader WHERE Grader in ('A','B','C','D','E')
UNION ALL
SELECT MAX(Grader),SUM(Graded_Items) FROM Grader WHERE Grader in ('A','B','C','D','E','F')

But, my problem is the table is keep on changing such as the GraderName is populating at the same time its corresponding Graded_Items. Is there any other ways to achieve my expected result?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Rigel1121
  • 2,022
  • 1
  • 17
  • 24
  • possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) – PM 77-1 Mar 04 '15 at 01:56

1 Answers1

4

Use Sum over() trick to get the running total

select GraderName, 
       sum(Graded_Items) over(order by GraderName) As Graded_Items
From yourtable
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172