I want to count number of matches between two teams.
select home.home_team, home.away_team, (home_matches_against away_matches_against) as matches
from
(select home_team, away_team, count(home_team) as home_matches_against
from results
group by home_team, away_team) as home
join
(select away_team, home_team, count(away_team) as away_matches_against
from results
group by away_team, home_team) as away
on home.home_team=away.away_team and home.away_team=away.home_team
output:
As you can see count of matches between tow teams are duplicated.
My desired result is for example:
home team away _team matches
England Scotland 117
Wales Scotland 106
and etc. I dont want count of matches to be duplicated
CodePudding user response：
You could add WHERE clause:
WHERE home.home_team < home.away_team;
CodePudding user response：
You could use LEAST and GREATEST. Something like:
SELECT t1.*
FROM ( SELECT LEAST(home_team, away_team) AS home_team,
GREATEST(home_team, away_team) AS away_team,
matches
FROM results
) AS t1
GROUP BY t1.home_team, t1.away_team,t1.matches;