sqlmysqlunion

Count number of occurrences of same value over multiple tables with different column names


this is the problem. I have two tables - ps5 and xbox:-

id ps5_game
John COD
Jean GTA
Kash COD
Don PVZ
Trey GOW
Nate UC
id xbox_game
Jarvis UC
Carl GOW
Damon COD
Donny GOW
Trav GOW
Natty UC

Now, I need to get the number of times of occurrences of each of the values in ps5_game and xbox_game spread over two tables, sorted in descending order. The values under id don't matter. The expected result is somewhat like this:-

game occurrence
GOW 4
COD 3
UC 3
GTA 1
PVZ 1

This is what I have tried:-

(SELECT 
  ps5_game, COUNT(*) AS occ 
  FROM ps5 
  GROUP BY ps5_game
) 
UNION 
(SELECT 
  xbox_game, COUNT(*) AS occ 3
  FROM xbox 
  GROUP BY xbox_game
)
ORDER BY occ DESC

Now, the issue is that I do get the occurrences but this doesn't merge the occurrences of the same game. So, instead of GOW showing 4, I have the following:-

ps5_game occ
GOW 1
COD 1
GOW 3

So, instead of adding the 1+3 from the two tables, it is being shown separately. Can anyone tell me how it can be achieved?


Solution

  • SELECT game, COUNT(*) AS occ
    FROM (
      SELECT ps5_game AS game FROM ps5
     UNION ALL
      SELECT xbox_game FROM xbox
    ) as g
    GROUP BY game
    ORDER BY occ;
    

    Output, tested on MySQL 8.4.3:

    +------+-----+
    | game | occ |
    +------+-----+
    | GTA  |   1 |
    | PVZ  |   1 |
    | COD  |   3 |
    | UC   |   3 |
    | GOW  |   4 |
    +------+-----+