MySQL Showing Count with 2 Tables Without Grouping-CodePudding

I have 2 tables and I am attempting to show the count for each item_id to show how many related items they have from their group_id number.

table_1 | item_id | item_name | |---------|--------------| | 1 | Red Scarf | | 2 | Blue Scarf | | 3 | Yellow Socks | | 4 | Blue Socks | | 5 | Brown Socks |

table_2 | item_id | group_id | |---------|----------| | 1 | 1 | | 2 | 1 | | 3 | 2 | | 4 | 2 | | 5 | 2 |

desired_table_output | item_id | group_id | number_in_group | |---------|----------|----------------------| | 1 | 1 | 2 | | 2 | 1 | 2 | | 3 | 2 | 3 | | 4 | 2 | 3 | | 5 | 2 | 3 |

The closest related question I found was from here: count without group but none of the examples seem get the desired output.

-- Failed Method #1

SELECT
  t.name,
  t.phone,
  COUNT('x') OVER (PARTITION BY t.name) AS namecounter
FROM
  Guys t

This won't work as my version is 5.7

-- Failed Method #2 http://sqlfiddle.com/#!9/e6f1cd/148

SELECT
t1.item_id,
t1.item_name,
t2.counter
FROM table_1 t1
INNER JOIN (
SELECT item_id, COUNT(*) AS number_in_group
  FROM table_2
  GROUP BY group_id
) t2
ON t1.item_id = t2.item_id

This displays only the first row that has a count - but I need each row to have it.

-- Failed Method #3 http://sqlfiddle.com/#!9/e6f1cd/150

SELECT
t1.item_id,
t1.item_name,
t2.counter
FROM table_1 t1
INNER JOIN (
SELECT item_id, COUNT(*) AS number_in_group
  FROM table_2
  GROUP BY item_id
) t2
ON t1.item_id = t2.item_id

This shows each item_id, but shows the count as 1 for each.

CodePudding user response：

This might work for you

SELECT
t1.item_id,
t1.item_name,
g.counter
FROM table_1 t1
JOIN table_2 t2 ON t1.item_id = t2.item_id
JOIN (
  SELECT group_id, COUNT(*) AS counter
  FROM table_2
  GROUP BY group_id
) g ON t2.group_id = g.group_id

http://sqlfiddle.com/#!9/e6f1cd/172

CodePudding user response：

You can use left join using the second query like this

SELECT
t1.item_id,
t1.item_name,
t2.counter
FROM table_1 t1
LEFT JOIN (
SELECT item_id, COUNT(*) AS counter
  FROM table_2
  GROUP BY group_id
) t2
ON t1.item_id = t2.item_id