CODEWITHSUNDEEP
mysqlsql
Purus
Purus

Reputation: 5799

SQL for getting match records of 2 teams

I am having a table with a list of games and it has the team Id's and the score in the game.

Here is the table structure.

ID  HOMETEAM    HOMETEAMSCORE   AWAYTEAM    AWAYTEAMSCORE

I need to get the game records of 2 particular teams. For example, for team 1 and 2, I would need the output as 1-3, which mean team 1 has won once against team 2 and team 2 has won 3 times against team 1.

I am using the below SQL and it works to some extend. But I need to get output as 0-0 for the games which has not yet started (scores of 0-0).

SELECT least(homeTeam, awayTeam) team1,
       greatest(homeTeam, awayTeam) team2,
       sum(case when awayTeam > homeTeam
          then case when homeTeamScore > awayTeamScore then 1 else 0 end else case when homeTeamScore > awayTeamScore then 0 else 1 end
       end) team1Wins,
       sum(case when hometeam > awayteam
          then case when homeTeamScore > awayTeamScore then 1 else 0 end else case when homeTeamScore > awayTeamScore then 0 else 1 end
       end) team2Wins
FROM ow_sports_games 
GROUP BY least(homeTeam, awayTeam),
         greatest(homeTeam, awayTeam)

Also is there any better way to have a better SQL other than this?

SQL Fiddle: http://sqlfiddle.com/#!2/10326/4/1

EDIT:

Some sample data to explain my requirement in more detail:

HOMETEAM    HOMETEAMSCORE   AWAYTEAM    AWAYTEAMSCORE
18          1               22              0
22          2               18              1
18          3               22              2
12          0               13              0

For the teams 18,22 the output should be 2-1 as there is 2 win for team 18 and 1 for team 22

For team 12 and 13 the output should be 0-0 as no games has been finished.

OUTPUT sample to explain overall scenario:

HomeTeam      AwayTeam     Records
18             22          2-1
22             18          1-2
12             13          0-0

Upvotes: 0

Views: 3081

Answers (1)

AgRizzo
AgRizzo

Reputation: 5271

This matches your output in the problem statement (the first 3 columns are identical, the next two are for sorting and making sure I took into account when they haven't played but the had a record. See http://sqlfiddle.com/#!2/10326/43

SELECT DISTINCT hometeam, awayteam
    , CONCAT(IF(hometeam = LEAST(hometeam, awayteam),team1wins, team2wins),' - ', IF(awayteam = LEAST(hometeam, awayteam),team1wins, team2wins)) AS Head2HeadRecord
    , CONCAT(LEAST(hometeam, awayteam), GREATEST(hometeam, awayteam)) AS surrogateSort
    , notplayed
FROM ow_sports_games 
JOIN (SELECT LEAST(hometeam, awayteam) AS team1
       ,GREATEST(hometeam, awayteam) AS team2
       ,SUM(IF(LEAST(hometeam, awayteam) = hometeam AND homeTeamScore > awayTeamScore, 1,
                IF(LEAST(hometeam, awayteam) = awayteam AND awayTeamScore > homeTeamScore,1,0) 
              )
            ) AS team1Wins
      ,SUM(IF(GREATEST(hometeam, awayteam) = hometeam AND homeTeamScore > awayTeamScore, 1,
                IF(GREATEST(hometeam, awayteam) = awayteam AND awayTeamScore > homeTeamScore,1,0) 
              )
            ) AS team2Wins  
       ,SUM(IF(homeTeamScore=0 AND awayTeamScore=0,1,0)) AS notPlayed
      FROM ow_sports_games 
      GROUP BY team1, team2) AS trecords
  ON LEAST(hometeam, awayteam) = team1
    AND GREATEST(hometeam, awayteam) = team2
ORDER BY surrogateSort, hometeam,awayteam;

Upvotes: 1

Related Questions