Computing Team Standings

12.8.1 Problem

You want to compute team standings from their win-loss records, including the games-behind (GB) values.

12.8.2 Solution

Determine which team is in first place, then join that result to the original records.

12.8.3 Discussion

Standings for sports teams that compete against each other typically are ranked according to who has the best win-loss record, and the teams not in first place are assigned a "games-behind" value indicating how many games out of first place they are. This section shows how to calculate those values. The first example uses a table containing a single set of team records, to illustrate the logic of the calculations. The second example uses a table containing several sets of records; in this case, it's necessary to use a join to perform the calculations independently for each group of teams.

Consider the following table, standings1, which contains a single set of baseball team records (they represent the final standings for the Northern League in the year 1902):

mysql> SELECT team, wins, losses FROM standings1
 -> ORDER BY wins-losses DESC;
+-------------+------+--------+
| team | wins | losses |
+-------------+------+--------+
| Winnipeg | 37 | 20 |
| Crookston | 31 | 25 |
| Fargo | 30 | 26 |
| Grand Forks | 28 | 26 |
| Devils Lake | 19 | 31 |
| Cavalier | 15 | 32 |
+-------------+------+--------+

The records are sorted by the win-loss differential, which is how to place teams in order from first place to last place. But displays of team standings typically include each team's winning percentage and a figure indicating how many games behind the leader all the other teams are. So let's add that information to the output. Calculating the percentage is easy. It's the ratio of wins to total games played and can be determined using this expression:

wins / (wins + losses)

If you want to perform standings calculations under conditions when a team may not have played any games yet, that expression evaluates to NULL because it involves a division by zero. For simplicity, I'll assume a nonzero number of games, but if you want to handle this condition by mapping NULL to zero, generalize the expression as follows:

IFNULL(wins / (wins + losses),0)

or as:

wins / IF(wins=0,1,wins + losses)

Determining the games-behind value is a little trickier. It's based on the relationship of the win-loss records for two teams, calculated as the average of two values:

• The number of games the second place team must win to have the same number of wins as the first place team
• The number of games the first place team must lose to have the same number of losses as the second place team

For example, suppose two teams A and B have the following win-loss records:

+------+------+--------+
| team | wins | losses |
+------+------+--------+
| A | 17 | 11 |
| B | 14 | 12 |
+------+------+--------+

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 can be expressed like this:

((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, all 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 query:

mysql> SELECT @wl_diff := MAX(wins-losses) FROM standings1;
+------------------------------+
| @wl_diff := MAX(wins-losses) |
+------------------------------+
| 17 |
+------------------------------+

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.65 | 0 |
| Crookston | 31 | 25 | 0.55 | 5.5 |
| Fargo | 30 | 26 | 0.54 | 6.5 |
| Grand Forks | 28 | 26 | 0.52 | 7.5 |
| Devils Lake | 19 | 31 | 0.38 | 14.5 |
| Cavalier | 15 | 32 | 0.32 | 17 |
+-------------+------+------+------+------+

There are a couple of minor formatting issues that can be addressed at this point. Percentages in standings generally are displayed to three decimals, and the GB value for the first place team is displayed as - rather than as 0. To display three decimals, TRUNCATE(expr,3) can be used. To display the GB value for the first place team appropriately, put the expression that calculates the GB column within a call to IF( ) 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)) = 0,'-',(@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.649 | - |
| Crookston | 31 | 25 | 0.553 | 5.5 |
| Fargo | 30 | 26 | 0.535 | 6.5 |
| Grand Forks | 28 | 26 | 0.518 | 7.5 |
| Devils Lake | 19 | 31 | 0.380 | 14.5 |
| Cavalier | 15 | 32 | 0.319 | 17 |
+-------------+------+------+-------+------+

These queries order the teams by win-loss differential, using winning percentage as a tie-breaker in case there are teams with the same differential value. It would be simpler just to sort by percentage, of course, but then you wouldn't always get the correct ordering. It's a curious fact that a team with a lower winning percentage can actually be higher in the standings than a team with a higher percentage. (This generally occurs early in the season, when teams may have played highly disparate numbers of games, relatively speaking.) Consider the case where two teams A and B have the following records:

+------+------+--------+
| team | wins | losses |
+------+------+--------+
| A | 4 | 1 |
| B | 2 | 0 |
+------+------+--------+

Applying the GB and percentage calculations to these team records yields the following result, where the first place team actually has a lower winning percentage than the second place team:

+------+------+------+-------+------+
| team | W | L | PCT | GB |
+------+------+------+-------+------+
| A | 4 | 1 | 0.800 | - |
| B | 2 | 0 | 1.000 | 0.5 |
+------+------+------+-------+------+

The standings calculations shown thus far can be done without a join. They involve only a single set of team records, so the first place team's win-loss differential can be stored in a variable. A more complex situation occurs when a dataset includes several sets of team records. For example, the 1997 Northern League had two divisions (Eastern and Western). In addition, separate standings were maintained for the first and second halves of the season, because season-half winners in each division played each other for the right to compete in the league championship. The following table, standings2, shows what these records look like, ordered by season half, division, and win-loss differential:

mysql> SELECT half, div, team, wins, losses FROM standings2
 -> ORDER BY half, div, wins-losses DESC;
+------+---------+-----------------+------+--------+
| half | div | team | wins | losses |
+------+---------+-----------------+------+--------+
| 1 | Eastern | St. Paul | 24 | 18 |
| 1 | Eastern | Thunder Bay | 18 | 24 |
| 1 | Eastern | Duluth-Superior | 17 | 24 |
| 1 | Eastern | Madison | 15 | 27 |
| 1 | Western | Winnipeg | 29 | 12 |
| 1 | Western | Sioux City | 28 | 14 |
| 1 | Western | Fargo-Moorhead | 21 | 21 |
| 1 | Western | Sioux Falls | 15 | 27 |
| 2 | Eastern | Duluth-Superior | 22 | 20 |
| 2 | Eastern | St. Paul | 21 | 21 |
| 2 | Eastern | Madison | 19 | 23 |
| 2 | Eastern | Thunder Bay | 18 | 24 |
| 2 | Western | Fargo-Moorhead | 26 | 16 |
| 2 | Western | Winnipeg | 24 | 18 |
| 2 | Western | Sioux City | 22 | 20 |
| 2 | Western | Sioux Falls | 16 | 26 |
+------+---------+-----------------+------+--------+

Generating the standings for these records requires computing the GB values separately for each of the four combinations of season half and division. Begin by calculating the win-loss differential for the first place team in each group and saving the values into a separate firstplace table:

mysql> CREATE TABLE firstplace
 -> SELECT half, div, MAX(wins-losses) AS wl_diff
 -> FROM standings2
 -> GROUP BY half, div;

Then join the firstplace table to the original standings, associating each team record with the proper win-loss differential to compute its GB value:

mysql> SELECT wl.half, wl.div, wl.team, wl.wins AS W, wl.losses AS L,
 -> TRUNCATE(wl.wins/(wl.wins+wl.losses),3) AS PCT,
 -> IF((fp.wl_diff - (wl.wins-wl.losses)) = 0,
 -> '-', (fp.wl_diff - (wl.wins-wl.losses)) / 2) AS GB
 -> FROM standings2 AS wl, firstplace AS fp
 -> WHERE wl.half = fp.half AND wl.div = fp.div
 -> ORDER BY wl.half, wl.div, wl.wins-wl.losses DESC, PCT DESC;
+------+---------+-----------------+------+------+-------+-------+
| half | div | team | W | L | PCT | GB |
+------+---------+-----------------+------+------+-------+-------+
| 1 | Eastern | St. Paul | 24 | 18 | 0.571 | - |
| 1 | Eastern | Thunder Bay | 18 | 24 | 0.428 | 6.00 |
| 1 | Eastern | Duluth-Superior | 17 | 24 | 0.414 | 6.50 |
| 1 | Eastern | Madison | 15 | 27 | 0.357 | 9.00 |
| 1 | Western | Winnipeg | 29 | 12 | 0.707 | - |
| 1 | Western | Sioux City | 28 | 14 | 0.666 | 1.50 |
| 1 | Western | Fargo-Moorhead | 21 | 21 | 0.500 | 8.50 |
| 1 | Western | Sioux Falls | 15 | 27 | 0.357 | 14.50 |
| 2 | Eastern | Duluth-Superior | 22 | 20 | 0.523 | - |
| 2 | Eastern | St. Paul | 21 | 21 | 0.500 | 1.00 |
| 2 | Eastern | Madison | 19 | 23 | 0.452 | 3.00 |
| 2 | Eastern | Thunder Bay | 18 | 24 | 0.428 | 4.00 |
| 2 | Western | Fargo-Moorhead | 26 | 16 | 0.619 | - |
| 2 | Western | Winnipeg | 24 | 18 | 0.571 | 2.00 |
| 2 | Western | Sioux City | 22 | 20 | 0.523 | 4.00 |
| 2 | Western | Sioux Falls | 16 | 26 | 0.380 | 10.00 |
+------+---------+-----------------+------+------+-------+-------+

That output is somewhat difficult to read, however. To make it easier to understand, you'd likely execute the query from within a program and reformat its results to display each set of team records separately. Here's some Perl code that does that by beginning a new output group each time it encounters a new group of standings. The code assumes that the join query has just been executed and that its results are available through the statement handle $sth:

my ($cur_half, $cur_div) = ("", "");
while (my ($half, $div, $team, $wins, $losses, $pct, $gb)
 = $sth->fetchrow_array ( ))
{
 if ($cur_half ne $half || $cur_div ne $div) # new group of standings?
 {
 # print standings header and remember new half/division values
 print "
$div Division, season half $half
";
 printf "%-20s %3s %3s %5s %s
", "Team", "W", "L", "PCT", "GB";
 $cur_half = $half;
 $cur_div = $div;
 }
 printf "%-20s %3d %3d %5s %s
", $team, $wins, $losses, $pct, $gb;
}

The reformatted output looks like this:

Eastern Division, season half 1
Team W L PCT GB
St. Paul 24 18 0.57 -
Thunder Bay 18 24 0.43 6.00
Duluth-Superior 17 24 0.41 6.50
Madison 15 27 0.36 9.00

Western Division, season half 1
Team W L PCT GB
Winnipeg 29 12 0.71 -
Sioux City 28 14 0.67 1.50
Fargo-Moorhead 21 21 0.50 8.50
Sioux Falls 15 27 0.36 14.50

Eastern Division, season half 2
Team W L PCT GB
Duluth-Superior 22 20 0.52 -
St. Paul 21 21 0.50 1.00
Madison 19 23 0.45 3.00
Thunder Bay 18 24 0.43 4.00

Western Division, season half 2
Team W L PCT GB
Fargo-Moorhead 26 16 0.62 -
Winnipeg 24 18 0.57 2.00
Sioux City 22 20 0.52 4.00
Sioux Falls 16 26 0.38 10.00

The code just shown that produces plain text output comes from the script calc_standings.pl in the joins directory of the recipes distribution. That directory also contains a PHP script, calc_standings.php, that takes the alternative approach of producing output in the form of HTML tables, which you might prefer for generating standings in a web environment.