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?
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 |
+------+-----+