How to order a sports league table in MySQL?
I already have a league table, but I want to take into account that 2 draws are equal to a win, and in a draw the lowest points against have the advantage.
Currently shown as below, but technically Team2 should have won:
Team W L T PF PA PCT
—————————————————————————————————
Team1 7 3 0 247 139 0.7000
Team2 6 2 2 220 122 0.6000
Team3 6 4 0 191 191 0.6000
Team4 4 5 1 167 201 0.4000
Team5 3 6 1 142 202 0.3000
Team6 2 8 0 193 305 0.2000
This is SQL:
SELECT team
, COUNT(*) played
, SUM(win) wins
, SUM(loss) lost
, SUM(win)/count(*) pctWon
, SUM(draw) draws
, SUM(SelfScore) ptsfor
, SUM(OpponentScore) ptsagainst
, SUM(SelfScore) - SUM(OpponentScore) goal_diff
, SUM(3*win + draw) score
FROM (
SELECT team
, SelfScore
, OpponentScore
, SelfScore > OpponentScore win
, SelfScore < OpponentScore loss
, SelfScore = OpponentScore draw
FROM (
SELECT HomeTeam team, HomeScore SelfScore, AwayScore OpponentScore
FROM Game
union all select AwayTeam, AwayScore, HomeScore
FROM Game
) a
) b
GROUP BY team
ORDER BY wins DESC, draws DESC, lost ASC, goal_diff DESC;
+3
source to share
2 answers
Take a look at the proposal ORDER BY
: you are really asking for the results to be ordered in order of wins. This rule takes precedence over all others, so it is obvious that Team1 will win.
ORDER BY wins DESC, draws DESC, lost ASC, goal_diff DESC;
I want to take into account that 2 draws are equal to a victory, and in a tie, the lowest points against have an advantage.
Then it would be:
ORDER BY (wins*2 + draws) DESC, lost ASC, ptsagainst DESC;
+1
source to share