4

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

0
2

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
5
  • What I am trying to get is a count of all of the combined, but based on your results I am liking the look of that. I am trying to now add a total coulmn too without re-repeating the entire selects (wiki_armors + wiki_items + wiki_weapons) as wiki_total. Is that possible to reuse them? Jan 2 '15 at 15:42
  • Yes. Simply add a new computed column in the SELECT. Add COALESCE(count_unlocks,0) + COALESCE(count_armors,0) + COALESCE(count_weapons,0)
    – JGA
    Jan 2 '15 at 15:47
  • I ditched my answer because if I corrected it, it would look like yours. So, +1 !!! Jan 2 '15 at 16:05
  • Thanks. I must admit that having to correct the one with the highest reputation was a bit intimidating for me. :-)
    – JGA
    Jan 2 '15 at 23:12
  • Incredible, thanks so much everyone for the help!!! Looks great :) and now I know for future. Jan 4 '15 at 5:47
2

I did not manage to delete my post from the phone. 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
  • I see two errors in this query. First, characters.name is not included in the GROUP BY so it can not be in the SELECT. This can be easily fixed including it in the GROUP BY. Secondly and more important. Counts are not correct. Imagine for eaxample that 'player 1' has two items and two armors. Doing the two left joins produce 4 results and if you count them you get 4 items and 4 armorsa which is not correct.
    – JGA
    Jan 2 '15 at 11:05
  • You are correct, I'll remove the answer since I don't have time to correct it now
    – Lennart
    Jan 2 '15 at 11:27
  • Ok, now the solution is valid. I undo the negative vote.
    – JGA
    Jan 2 '15 at 13:23
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.