pandasql: count occurrences of pairs

Question

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

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.

Answer 1

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