Database Reference
In-Depth Information
Here, team B has to win three more games, and team A has to lose one more game for
the teams to be even. The average of three and one is two, thus B is two games behind
A. Mathematically, the games-behind calculation for the two teams is:
((winsA - winsB) + (lossesB - lossesA)) / 2
With a little rearrangement of terms, the expression becomes:
((winsA - lossesA) - (winsB - lossesB)) / 2
The second expression is equivalent to the first, but it has each factor written as a single
team's win-loss differential, rather than as a comparison between teams. That makes it
easier to work with because each factor can be determined independently from a single
team record. The first factor represents the first-place team's win-loss differential, so if
we calculate that value first, the other team GB values can be determined in relation to
it.
The first-place team is the one with the largest win-loss differential. To find that value
and save it in a variable, use this statement:
mysql>
SET @wl_diff = (SELECT MAX(wins-losses) FROM standings1);
Then use the differential as follows to produce team standings that include winning
percentage and GB values:
mysql>
SELECT team, wins AS W, losses AS L,
->
wins/(wins+losses) AS PCT,
->
(@wl_diff - (wins-losses)) / 2 AS GB
->
FROM standings1
->
ORDER BY wins-losses DESC, PCT DESC;
+-------------+------+------+--------+---------+
| team | W | L | PCT | GB |
+-------------+------+------+--------+---------+
| Winnipeg | 37 | 20 | 0.6491 | 0.0000 |
| Crookston | 31 | 25 | 0.5536 | 5.5000 |
| Fargo | 30 | 26 | 0.5357 | 6.5000 |
| Grand Forks | 28 | 26 | 0.5185 | 7.5000 |
| Devils Lake | 19 | 31 | 0.3800 | 14.5000 |
| Cavalier | 15 | 32 | 0.3191 | 17.0000 |
+-------------+------+------+--------+---------+
There are a couple minor formatting issues to address at this point. Typically, standings
listings display percentages to three decimal places, and the GB value to one decimal
place (except that the GB value for the first-place team is displayed as
-
). To display
n
decimal places, use
TRUNCATE(
expr
,
n
).
To display the GB value for the first-place team
appropriately, use an
IF()
expression that maps 0 to a dash:
mysql>
SELECT team, wins AS W, losses AS L,
->
TRUNCATE(wins/(wins+losses),3) AS PCT,
->
IF(@wl_diff = wins-losses,
->
'-',TRUNCATE((@wl_diff - (wins-losses))/2,1)) AS GB
->
FROM standings1
