MongoUK - 2 months ago 10

PHP Question

I already have a league standings table, however, I want to make it take into account that 2 draws are equal to a win, and in a tie, lowest points against takes precedence.

Currently shows like 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 the 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;

Answer

Take a look at your `ORDER BY`

clause: you are effectively asking for the results to be ordered by order of wins descending. This rule takes preference over all the others, so obviously Team1 wins.

```
ORDER BY wins DESC, draws DESC, lost ASC, goal_diff DESC;
```

I want to make it take into account that 2 draws are equal to a win, and in a tie, lowest points against takes precedence.

Then that would be:

```
ORDER BY (wins*2 + draws) DESC, lost ASC, ptsagainst DESC;
```