I have below table and want to find out a count and group by that will show the number of times republic of ireland played a particular opponent regardless if they are the home team or away team
I want something that will show like the below
x
OPPONENT TIMES PLAYED SCOTLAND 2 UKRAINE 2 ARMENIA 2 LITHUANIA 1Thanks for help!
Advertisement
Answer
This is how I would do it — normalize the table with a UNION ALL and then group by and count
SELECT sub.team_b, count(*) as times_played FROM ( SELEECT home_team as team_a, away_team as team_b FROM table WHERE home_team = 'Republic of Ireland' UNION ALL SELEECT away_team as team_a, home_team as team_b FROM table WHERE away_team = 'Republic of Ireland' ) AS sub GROUP BY sub.team_bIf you know that every record has ‘Republic of Ireland’ as one of the teams, then you don’t need to check if it exists only which is which like this:
SELECT CASE WHEN home_team = '' THEN home_team ELSE away_team END as OPPONENT, count(*) as times_played FROM table GROUP BY CASE WHEN home_team = '' THEN home_team ELSE away_team END