Question

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 :D

3 Answers

1. 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

2. It's probably easiest to do each count as a subselect:

SELECT c.name
     , (select COUNT(i.character_id) 
         From wiki_items i
         Where   c.character_id=i.character_id

       ) as  wiki_unlocks
     , (select COUNT(a.character_id) 
         From wiki_armors a
         Where   c.character_id=a.character_id

       ) as  wiki_armors
     , (select COUNT(w.character_id) 
         From wiki_weapons w
         Where   c.character_id=w.character_id

       ) as  wiki_weapons  

FROM characters c

3. Maybe it helps:

SELECT
    Sum( a.count )
FROM(
    SELECT Count( * ) AS count FROM Table1
    UNION ALL
    SELECT Count( * ) AS count FROM Table2
    UNION ALL
    SELECT Count( * ) AS count FROM Table3
    UNION ALL
    SELECT Count( * ) AS count FROM Table4
) a

内容来源于Database Administrators Stack Exchange, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接：Count multiple tables as one count