Count and Group by 2 different columns SQL-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

SELECT target_col, count(*) AS cnt
FROM (
    SELECT 
     if (home_team = 'Republic of Ireland', away_team, home_team) AS target_col
    FROM table
    ) tb
GROUP BY target_col