cumulative sum till target reached in mysql

Question

I have following tables,

Workout Data:

Date       User        Distance     Calories        
1614944833   1           100            32
1614944232   2           100            43
1624944831   1           150            23
1615944832   3           250            63
1614644836   1           500            234 
1614954835   2           100            55
1614344834   3           100            34
1614964831   1           260            23
1614944238   1           200            44

user_subdomain Data:

    User        sub_domain
    1           3
    2           3           
    3           3
    4           2

Subdomain data:

    subdomain       name
    3               test1 
    4               test2

I would like to get sum value of distance,calories,count of records once they user reached sum of distance >= 1000.we should not count remaining records if user crossed 1000 distance.( if user crossed 1000,then 1000 else max distance value).

Expected Output:

Date       record_count Distance    Calories    
1614964831   4          1000        312
1614954835   2          200         98
1614344834   3          350         97

So This result shows each users total effort they used to reach distance 1000 by record_count,then if they reached 1000 above then calculated as 1000,else max reached distance value,then total sum of that calories till 1000 cumulative sum reached.This is the output i need to retrieve.I tried with below query,but not works

Can anyone suggest with cumulative sum inner join method or any other solution for this?

yes,but not able to skip remaining records if user reached 1000.that is isue — Kgeorj Tom, Dec 01 '21 at 02:59
Distance over 1000 can be handled by display logic, or maybe MAX. Do you want to ignore calories after a distance of 1000? — Chris Haas, Dec 01 '21 at 03:32
yes,above workout table have 5 records for userid 1.that user crossed 1000 by first 4 records,so need to sum calories for first 4 records and distance as 1000,then record_count as 4.like that i need to retrieve,last 5th record will be skipped in this calculation — Kgeorj Tom, Dec 01 '21 at 03:40
please clarify the order to read records, the expected result does not order by any of the columns. — ProDec, Dec 01 '21 at 07:18
Check out the answers here: https://stackoverflow.com/q/12113699/231316 — Chris Haas, Dec 01 '21 at 12:44

score 1 · Answer 1 · answered Dec 01 '21 at 12:46

1

Since MySQL 8.0 you can use window functions in next way:

with cumulative as (
  -- calculate cumulative Distance & Calories
  select 
      User,
      Distance,
      Calories,
      sum(Distance) over (partition by User order by Date) SumDistance,
      sum(Calories) over (partition by User order by Date) SumCalories
  from Workout
  order by User, Date
) select 
    User, max(SumDistance), max(SumCalories)
from cumulative
where SumDistance - Distance < 1000 -- filter
group by User;

MySQL window functions

answered Dec 01 '21 at 12:46

Slava Rozhnev

9,510
6
23
39

that gets really close, but I think they also want to throw away calories when the distance has been reached. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=236cb115bcd1279d83cb26ed88a735a3 – Chris Haas Dec 01 '21 at 18:18
this query not works in phpmyadmin and shows syntax error – Kgeorj Tom Dec 04 '21 at 14:05
What MySQL version you have? – Slava Rozhnev Dec 04 '21 at 18:15

cumulative sum till target reached in mysql

1 Answers1