-1

I was trying to count the number of matches that A and B have ever played, the dataset looks like this:

This is how the data looks like in Notebook

so the number of matches team1 and team 29 have played is 2 as they each once were HomeTeam and AwayTeam, however, using my query I could only count it as one:

SELECT HomeTeamID, AwayTeamID, Count(*) AS num_matches
FROM games GROUP BY HomeTeamID, AwayTeamID

I know where my problem is but don't know how to solve it.

Salman A
  • 262,204
  • 82
  • 430
  • 521
Kristy.Z
  • 27
  • 3
  • With that data, can you post your expected output? – Blue Nov 23 '18 at 20:48
  • @FrankerZ She said the expected output is 2 for teams 1 and 29. – Barmar Nov 23 '18 at 20:50
  • Where is Team 29 the Home Team in your sample dataset? – Barmar Nov 23 '18 at 20:52
  • Yeah sure, so the task is asked to write in sql languages but in python notebook, so it should return 3 columns, the focal team id, the opposing team ids that have play against the focal team, and the number of times that the two teams have played. I hope it makes sense! – Kristy.Z Nov 23 '18 at 20:53
  • There's nothing in the question about a focal team, and the query you tried doesn't match a specific team ID. – Barmar Nov 23 '18 at 20:54

1 Answers1

0

Put the teams in a common order so the grouping isn't sensitive to whether they're home or away.

SELECT GREATEST(HomeTeamID, AwayTeamID) AS team1, LEAST(HomeTeamID, AwayTeamID) AS team2, COUNT(*) as num_matches
FROM games
GROUP BY team1, team2
Barmar
  • 741,623
  • 53
  • 500
  • 612