[SQL] How to count items from player_items and player_depotitems?

[okurde]

Hello, does anyone know how to count items from player_items and player_depotitems?

I'm trying with:

SELECT
        `player_items`.`itemtype`, SUM(`player_items`.`count`) AS count,
        `player_depotitems`.`itemtype`, SUM(`player_depotitems`.`count`) AS count
FROM
        `player_items`, `player_depotitems`
WHERE
        (`player_items`.`player_id` = 25 OR
        `player_depotitems`.`player_id` = 25) AND
        (`player_items`.`itemtype` = 2160 OR
        `player_depotitems`.`itemtype` = 2160)
GROUP BY
        `player_depotitems`.`itemtype`;

But it counts really weird. Thanks in advance

 

[Engradiel]

This will generate a inventary ranking of item crystal coin with id 2160
Just change player_items to depot_items or inbox_items and change item id in itemtype

SELECT player_id, p.name, SUM(count) as total_count FROM player_items INNER JOIN players p ON p.id = player_id WHERE itemtype = 2160 GROUP BY player_id ORDER BY total_count DESC limit 5

 

[Gesior.pl]

Same code for total count of player_items, player_depotitems and player_inboxitems:

SELECT allitems.player_id, p.name, SUM(allitems.count) as total_count
FROM
(
SELECT player_id, count FROM player_items WHERE itemtype = 2160
UNION ALL
SELECT player_id, count FROM player_depotitems WHERE itemtype = 2160
UNION ALL
SELECT player_id, count FROM player_inboxitems WHERE itemtype = 2160
) allitems
INNER JOIN players p ON p.id = allitems.player_id GROUP BY allitems.player_id ORDER BY total_count DESC limit 5

But it counts really weird. Thanks in advance

It counts weird, because you get results from 2 tables (player_items and player_depotitems), but group them by 1 table (player_depotitems.itemtype).
You can't group by 2 tables and get right results (grouping by few columns at once is possible in SQL, but you won't get right results anyway). Solution is to first UNION ALL results from multiple tables and then group them by player ID.