Football League Table - Last 5 matches

[runnerjp2003]

Hi all,

Does anyone know how I could alter the MYSQL code to select the last 5 games each Team has played to create a table of using the last 5 games to show current form?

 

My current code is as follows:

    select 
    team, 
    count(*) played, 
    count(case when scor1 > scor2 then 1 end) wins, 
    count(case when scor2> scor1 then 1 end) lost, 
    count(case when scor1 = scor2 then 1 end) draws, 
    sum(scor1) scor1, 
    sum(scor2) scor2, 
    sum(scor1) - sum(scor2) goal_diff,
    sum(
          case when scor1 > scor2 then 3 else 0 end 
        + case when scor1 = scor2 then 1 else 0 end
    ) score 
from (
  select  txtechipa1 team, scor1, scor2 from footballtest INNER JOIN Leagues ON LEFT(footballtest.codmeci, 4) = Leagues.code where Leagues.Code = '0101' and   footballtest.sezonul = '19' and datameci < CURDATE()  
  union all
    select  txtechipa2, scor2, scor1 from footballtest  INNER JOIN Leagues ON  LEFT(footballtest.codmeci, 4) = Leagues.code where Leagues.Code = '0101' and   footballtest.sezonul = '19' and datameci < CURDATE()  
) a
group by team
order by score desc , goal_diff  

 

[Barand]

Knowledge of the relevant table structure greatly assists when trying to write queries.