Query to Calculate Wins/Losses/Ties
- Home
- SQL
- Query to Calculate Wins/Losses/Ties
For tracking sports game results. Each game has two teams. Home team and visiting team. Outcome can be win/loss/tie. A win is 2 points, loss is 0, and a tie is 1 point.
Two base tables:
TEAM
Id (pk)
TeamName
GAME
Id (pk)
HomeTeamId (fk)
VisitorTeamId (fk)
HomeScore
VisitorScore
Goal OutPut is something like:
Team | Games Played | Wins | Losses | Ties | Points |
A | 2 | 1 | 0 | 1 | 3 |
B | 2 | 1 | 1 | 0 | 2 |
C | 2 | 0 | 1 | 1 | 1 |
SELECT *, (2 * Wins + 1 * Ties) AS Points
FROM
(SELECT t.TeamName, COUNT(*) AS GamesPlayed,
SUM(CASE
WHEN g.HomeTeamID = t.Id AND g.HomeScore > g.VistorScore THEN 1
WHEN g.VisitorTeamID = t.Id AND g.HomeScore < g.VistorScore THEN 1
ELSE 0
END) AS Wins,
SUM(CASE
WHEN g.HomeTeamID = t.Id AND g.HomeScore < g.VistorScore THEN 1
WHEN g.VisitorTeamID = t.Id AND g.HomeScore > g.VistorScore THEN 1
ELSE 0
END) AS Losses,
SUM(CASE
WHEN g.HomeScore = g.VistorScore THEN 1
ELSE 0
END) AS Ties
FROM GAME as g
JOIN TEAM as t ON g.HomeTeamID = t.Id OR g.VisitorTeamID = t.Id
GROUP BY t.TeamName) AS a
TSQL MSSQL Query W/L/T