Ordering a query then doing a running total

Databases
1

Hi There,

I would appreciate some help on a query that is beating me at the moment. I have data in a table I would like to first put in order and then calculate a running total. In the example below the query works and shows the ‘Cumulative’ column as ordered, however, I want the ‘ScoreC/ScoreD’ column to be in ascending order, which it currently isn’t. Once ‘ScoreC/ScoreD’ is ordered correctly I then want it to calculate the Cumulative column. Any help would be appreciated:

query = “SELECT colname, scoreA, scoreB, scoreC, ScoreC/ScoreD, ScoreD, (SELECT Sum(ScoreD) FROM mytable x WHERE x.ScoreC/ScoreD <= mytable.ScoreC/ScoreD) AS Cumulative FROM mytable ORDER BY Cumulative”

Colname: ScoreC:ScoreC/ScoreD:Cumulative
J : -1995 : -0.042 : 47062
P : 10700 : 0.055 : 244531
S : 10711 : 0 : 2212263
K : 16995 : 0.194 : 2221014
W : 27418 : 1.282 : 2223154
B : 187862 : 0.702 : 2249932

Thanks

2

If I change the query to be …ORDER BY ScoreC/ScoreD then I get the results in the correct order I want but the Cumulative column is then out of sync:

Colname: ScoreC:ScoreC/ScoreD:Cumulative
J : -1995 : -0.042 : 47062
S : 10711 : 0 : 2212263
P : 10700 : 0.055 : 244531
K : 16995 : 0.194 : 2221014
B : 187862 : 0.702 : 2249932
W : 27418 : 1.282 : 2223154

So somehow I need for it to ORDER BY ScoreC/ScoreD first and then calculate the Cumulative column after this?

query = “SELECT colname, scoreA, scoreB, scoreC, ScoreC/ScoreD, ScoreD, (SELECT Sum(ScoreD) FROM mytable x WHERE x.ScoreC/ScoreD <= mytable.ScoreC/ScoreD) AS Cumulative FROM mytable ORDER BY Cumulative”

3

so why aren’t those columns in your ORDER BY clause then?