I want to update my specific row with that row previous value, after searching i found some post about this but those solution not working with me. So what is wrong with my query or how can i archive my goal.?

This is my query:

select d.innings_no,
       (@total := @total + ifnull(sum(b.runs_scored) + sum(c.extra_runs), 0)) as Runs,
       count(b.runs_scored)                                                      Balls
from wicket_taken d
       inner join ball_by_ball a on d.match_id = a.match_id and d.player_out in (a.striker, a.non_striker)
       left join batsman_scored b
                 on (a.match_id, a.over_id, a.ball_id, a.innings_no) = (b.match_id, b.over_id, b.ball_id, b.innings_no)
       left join extra_runs c
                 on (a.match_id, a.over_id, a.ball_id, a.innings_no) = (c.match_id, c.over_id, c.ball_id, c.innings_no)
       inner join Player e on d.player_out = e.Player_Id
       cross join
       (select @total := 0) params
where d.match_id = 335987
group by d.player_out, d.innings_no
order by d.player_out asc;

This is my current output: enter image description here

But i want those to be like this (previous column runs + current column runs):

1 SC Ganguly 61 32

1 RT Ponting 112 (51+61) 41

1 DJ Hussey 172 (112+60) 30

So how can i solve this.?

  • Do you have MariaDB-10.2+ or MySQL-8.0? Because if you do Window functions provide an easier answer. Can you show your tables with sample data to support your output.
    – danblack
    Commented Feb 27, 2019 at 5:59
  • my db is MySQL-8+, actually in that query i'm joining 5tables, so i'm not sure which table can i show. also those table only for learning purpose.
    – Rhidoy
    Commented Feb 27, 2019 at 6:11
  • Take a look at this very quick intro to window functions
    – danblack
    Commented Feb 27, 2019 at 6:14
  • i'm checking those.
    – Rhidoy
    Commented Feb 27, 2019 at 6:20
  • Using user-defined variables: 1) with multiple tables 2) with group by 3) without proper ordering. Unpredictable result guaranteed. Obtain all another output fields and fields needed for UDV-calculations in subquery, then use UDV calculations in outer query with proper (unique!) records ordering.
    – Akina
    Commented Feb 27, 2019 at 6:34


Your Answer

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

Browse other questions tagged or ask your own question.