Question 416110 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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?

Tags:

2

Answers


  1. 0

    This can be achieve by using a simple JOIN.

    SELECT 
  ps5_game, COUNT(*) AS occ 
FROM ps5
JOIN 
  xbox_game ON xbox_game.xbox_game = ps5_game.ps5_game
GROUP BY ps5_game
    Login or Signup to reply.
  2. 0 
    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 |
+------+-----+
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search