runnerjp2003 Posted April 9, 2019 Share Posted April 9, 2019 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 Link to comment Share on other sites More sharing options...
Barand Posted April 9, 2019 Share Posted April 9, 2019 Knowledge of the relevant table structure greatly assists when trying to write queries. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.