MySQL - Sum rows in one column based on values in another column - grouped - using variables

Question

I am trying to sum data based on the values in one column grouped by the values in another column.

Specifically I am trying to sum data so that cumesum is reset to zero when the Ref column changes as in the below image.

Required Result

I can do this with this conventional query but it is too slow for a large recordset:

select LengthsBtn, Ref, HorseNo,
 (select sum(r2.LengthsBtn) from Runners r2 where r2.Fin_Pos <= r.Fin_Pos and r2.ref = r.ref) as cumesum from Runners r;

To speed this is up i am trying to use variables to make the same thing happen but i cannot figure out how to reset the variable to zero when the Ref changes using this query:

select Fin_Pos, HorseNo, Ref,
 (@sum := @sum + r.LengthsBtn) as cumesum from RUNNERS r cross join
 (select @sum := 0) params order by Ref, Fin_Pos;

However this query returns the results as below and is creating a cumulative column.

Wrong Result

Any advice much appreciated.

• Community: Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
• Charlieface: Sample data and expected results as text not images would help
• Rick James: What version? See Windowing functions in 8.0.

Answer 1

In newer versions of MySQL you can use a windowed running sum. You simply put the Ref column as the partitioning column, to calculate it individually per Ref.

select
 Fin_Pos,
 HorseNo,
 Ref,
 sum(r.LengthsBtn) over (partition by Ref order by Fin_Pos rows unbounded preceding) as cumesum
from RUNNERS r 
order by
 Ref,
 Fin_Pos;

Do not be tempted to use variables like you have, it can cause incorrect results and has undefined behaviour.

• pman: This is really helpful - I have been using 5.6 I will upgrade and test but assume your solution to be correct - thank you for your help on this much appreciated