Is there some way to join two of Union All columns

Question

im making League of Legends Api I have items in database like this database I have sql code like this to check how many times ppl bought these items in game

SELECT
Items.Item,
count(Items.Item) as repeats
FROM 
(
    SELECT Item0 AS Item FROM playergame
    UNION ALL
    SELECT Item1 AS Item FROM playergame
    UNION ALL
    SELECT Item2 AS Item FROM playergame
    UNION ALL
    SELECT Item3 AS Item FROM playergame
    UNION ALL
    SELECT Item4 AS Item FROM playergame
    UNION ALL
    SELECT Item5 AS Item FROM playergame
    UNION ALL
    SELECT Item6 AS Item FROM playergame
) AS Items 
GROUP BY
Items.Item  
ORDER BY `repeats`

This code gives me something like that:

result of sql

I want to add on the right side another table with repeats but this time add to code WHERE so i made this code:

SELECT
Items.Item,
count(Items.Item) as repeats,
count(ItemW.ItemW) as Wrepeats
FROM 
(
    SELECT Item0 AS Item FROM playergame
    UNION ALL
    SELECT Item1 AS Item FROM playergame
    UNION ALL
    SELECT Item2 AS Item FROM playergame
    UNION ALL
    SELECT Item3 AS Item FROM playergame
    UNION ALL
    SELECT Item4 AS Item FROM playergame
    UNION ALL
    SELECT Item5 AS Item FROM playergame
    UNION ALL
    SELECT Item6 AS Item FROM playergame
) AS Items 
INNER JOIN 
(
    SELECT Item0 AS ItemW FROM playergame
    UNION ALL
    SELECT Item1 AS ItemW FROM playergame WHERE Win = 1
    UNION ALL
    SELECT Item2 AS ItemW FROM playergame WHERE Win = 1
    UNION ALL
    SELECT Item3 AS ItemW FROM playergame WHERE Win = 1
    UNION ALL
    SELECT Item4 AS ItemW FROM playergame WHERE Win = 1
    UNION ALL
    SELECT Item5 AS ItemW FROM playergame WHERE Win = 1
    UNION ALL
    SELECT Item6 AS ItemW FROM playergame WHERE Win = 1
)AS ItemW
ON Items.Item = ItemW.ItemW
GROUP BY
Items.Item

from this sql i got this result second result of sql

score 1 · Accepted Answer · answered Dec 27 '18 at 20:44

I think you want:

SELECT i.Item, COUNT(*) as repeats,
       SUM(CASE WHEN Win = 1 THEN 1 ELSE 0 END) as Wins
FROM (SELECT Item0 AS Item, Win FROM playergame
      UNION ALL
      SELECT Item1 AS Item, Win FROM playergame
      UNION ALL
      SELECT Item2 AS Item, Win FROM playergame
      UNION ALL
      SELECT Item3 AS Item, Win FROM playergame
      UNION ALL
      SELECT Item4 AS Item, Win FROM playergame
      UNION ALL
      SELECT Item5 AS Item, Win FROM playergame
      UNION ALL
      SELECT Item6 AS Item, Win FROM playergame
     ) i 
GROUP BY i.Item  
ORDER BY `repeats`;

That said, you should fix your data structure. You should have another table called playergameItems with one row per "playergame" and one row per item.

Thanks u very much I was sitting on this by couple of hours. I will take ur advice and make new table. Thank u for teach me something new and for u timm. — Jakub Radtke, Dec 27 '18 at 20:57

Is there some way to join two of Union All columns

1 Answers1