Count and Group by 2 different columns SQL

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!

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_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