1

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.

3
  • 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.
    – Community Bot
    Commented Mar 13, 2023 at 23:21
  • Sample data and expected results as text not images would help Commented Mar 14, 2023 at 0:10
  • What version? See Windowing functions in 8.0.
    – Rick James
    Commented Mar 31, 2023 at 20:52

1 Answer 1

0

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.

1
  • 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
    – pman
    Commented Mar 15, 2023 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.