Okay, I haven't found a way to explain it on the title, but here it goes:

I have two tables as such - one called tb_items:

This table represents all the items available, with the name and rarity;

And another one called tb_inventories:

This table stores every item each user owns;

What I wanna do is count per user, how many items of each rarity they own compared to the total number of items of that rarity, all in one query. I don't know if that's possible, but for now I make separate queries and then put together all results, however that wouldn't be good using a bigger database. A possible result would look something like this:

I was thinking of making a separate table with the total count of each rarity and just compare with that table, but it looks like it's doable without that, I just can't figure out how...

for taking the total I was doing a simple select statement

SELECT rarity, count(rarity) from tb_items GROUP BY rarity

Then, for the inventory one I was doing this:

SELECT u.user_owned, c.rarity, COUNT(c.rarity)  FROM tb_items c
INNER JOIN tb_inventories u ON u.item_name= c.item_name
GROUP BY u.user_owned, c.rarity

Then I just treated the two results separately

CodePudding user response：

What you can do with your current attempt is make another query that acts as a reference table for your existing queries, like this:

SELECT DISTINCT rarity, user_owned
   FROM tb_items 
CROSS JOIN tb_inventories;

The results:

You can see that all user will get assigned with all rarity type regardless if the have it or not. This will be the main reference. Now you can combine all 3 queries by making them as a derived table (subquery) and use LEFT JOIN:

SELECT t1.user_owned AS 'User',
       t1.rarity AS 'Rarity',
       IFNULL(t3.ucr,0) AS 'Owned Count',
       t2.cr AS 'Total Count'
  FROM
(SELECT DISTINCT rarity, user_owned
   FROM tb_items 
CROSS JOIN tb_inventories) t1
LEFT JOIN
(SELECT rarity, count(rarity) AS cr 
  from tb_items 
  GROUP BY rarity) t2 ON t1.rarity=t2.rarity
LEFT JOIN
(SELECT u.user_owned, c.rarity, COUNT(c.rarity) AS ucr
  FROM tb_items c 
  INNER JOIN tb_inventories u ON u.item_name= c.item_name 
  GROUP BY u.user_owned, c.rarity) t3 
  ON t1.user_owned=t3.user_owned
  AND t1.rarity=t3.rarity;

Here's a demo fiddle;

An attempt using some functions only available to MySQL v8 :

WITH cte AS (
  SELECT DISTINCT rarity, user_owned
   FROM tb_items 
CROSS JOIN tb_inventories)
SELECT cte.user_owned AS 'User',
       cte.rarity AS 'Rarity',
       SUM(CASE WHEN t2.user_owned IS NOT NULL THEN 1 ELSE 0 END) AS 'Owned Count',
       t1.tr AS 'Total Count'
  FROM cte
  LEFT JOIN
(SELECT item_name, rarity, COUNT(rarity) OVER (PARTITION BY rarity) AS tr 
  FROM tb_items) t1 
ON cte.rarity=t1.rarity
LEFT JOIN tb_inventories t2
 ON t1.item_name=t2.item_name
 AND cte.user_owned=t2.user_owned
GROUP BY cte.user_owned, cte.rarity, t1.tr;