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
OPPONENT TIMES PLAYED SCOTLAND 2 UKRAINE 2 ARMENIA 2 LITHUANIA 1
Thanks for help!
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_b
If 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