MySQL – Count Multiple Tables as One

countMySQL

I have seen how to do multiple counts for different tables, but never how to combine them all into one. I have a MySQL DB where I am running the following query:

SELECT characters.name, COUNT(*) AS wiki_unlocks
    FROM wiki_items
    INNER JOIN characters
    ON characters.character_id=wiki_items.character_id
    GROUP BY wiki_items.character_id
    ORDER BY wiki_unlocks DESC
    LIMIT 10;

This is giving me the following which is great:

name          wiki_unlocks
player1       2
player2       1

I want to get a combined count of all of the 'wiki_xxxx' tables. For example I want 'wiki_items'(above) + 'wiki_armors' + 'wiki_weapons' + …

Thanks for any help 😀

Best Answer

If performance can be a problem, because tables have lots of rows, I would do this way. Grouping and counting first and joining tables next.

SELECT characters.name, 
COALESCE(count_unlocks,0) AS unlocks, 
COALESCE(count_armors,0) AS armors,
COALESCE(count_weapons,0) AS weapons,
COALESCE(count_unlocks,0) + COALESCE(count_armors,0) + COALESCE(count_weapons,0) AS total
FROM characters
LEFT JOIN 
(SELECT wiki_items.character_id, count(*) AS count_unlocks from wiki_items
GROUP BY wiki_items.character_id) AS wiki_unlocks
ON characters.character_id = wiki_unlocks.character_id
LEFT JOIN
(SELECT wiki_armors.character_id, count(*) AS count_armors from wiki_armors
GROUP BY wiki_armors.character_id) AS wiki_armors
ON characters.character_id = wiki_armors.character_id
LEFT JOIN
(SELECT wiki_weapons.character_id, count(*) AS count_weapons from wiki_weapons
GROUP BY wiki_weapons.character_id) AS wiki_weapons
ON characters.character_id = wiki_weapons.character_id
Related Question