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:
TeamGames PlayedWinsLossesTiesPoints
A21013
B21102
C20111


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


Back To Top
© 1998 - 2024 psacake.com
Version 7.21 | Advertise on this site