How to get field count Mysql

Question

Question title is senseles ,I know it. But my problem seems to diffucult to explain.

I have a one table on phpmyadmin like this

+-----+-----+----
| id |tag |item|
+=====+=====+===
| 1 |  1  | 132|
+-----+-----+---
| 2 |  2  | 132|
+-----+-----+---
| 3 |  2  | 134|
+-----+-----+---  
| 4 |  1  | 135|
+-----+-----+---

I want to find count of RELATED items.

For instance item-132 has 1,2 tag values and item-134 has 2 tag values. item-132 and item-134 has same 2 tag value. SO item-132 and item-134 has one related tag.

I want to get relation tags count like this ...

How can I set SQL query that get relation tag count in MySQL.

Can you give an example of the desired output? I found the question a bit hard to follow. — Mureinik
– Mureinik, Commented Dec 25, 2013 at 19:07

Dan Bracuk · Accepted Answer · 2013-12-25 19:09:57Z

1

A self join will get you started:

select t1.item item1, t2.item item2, etc
from table t1 join table t2 on t1.item <> t2.item
and t1.tag = t2.tag
where whatever

answered Dec 25, 2013 at 19:09

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joachim Isaksson · Accepted Answer · 2013-12-25 19:12:51Z

1

Provided that the tags only exist at most once per item, you can use a self join;

SELECT t2.item, COUNT(t2.id) cnt
FROM mytable t1 
JOIN mytable t2 
  ON t1.tag=t2.tag AND t1.item<>t2.item
WHERE t1.item = 132
GROUP BY t2.item

An SQLfiddle to test with.

answered Dec 25, 2013 at 19:12

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Comments

Eugen Rieck · Accepted Answer · 2013-12-25 19:09:44Z

0

Assuming you want the relation count between 132 and 134, you might want to consider

SELECT COUNT(*) AS relcount
FROM tablename AS aa
INNER JOIN tablename AS bb ON aa.tag=bb.tag
WHERE aa.item=132
AND bb.item=134

answered Dec 25, 2013 at 19:09

Eugen Rieck

65.6k10 gold badges75 silver badges97 bronze badges

1 Comment

user3083067 Over a year ago

Thank you Eugen its very usefull.

Leszek Andrukanis · Accepted Answer · 2013-12-25 20:10:12Z

0

You can do as below:

SELECT COUNT(*) FROM tt t1 
JOIN tt t2 ON t1.tag=t2.tag 
WHERE t1.item = 132 
AND t2.item = 134;

answered Dec 25, 2013 at 20:10

Leszek Andrukanis

2,1252 gold badges19 silver badges30 bronze badges

Comments

Tin Tran · Accepted Answer · 2013-12-25 21:50:37Z

0

sqlFiddle This will give you the related tag and the item1 and item2 (items that have this related tag)

SELECT T1.tag,T1.item as item1,T2.item as item2
FROM yourTable T1
INNER JOIN yourTable T2
ON (T1.tag = T2.tag AND T2.item > T1.item)

then if you want a count, you can add the COUNT(T1.tag) and GROUP BY item1,item2 like (sqlFiddle)

SELECT COUNT(T1.tag) as relatedCount,T1.item as item1,T2.item as item2
FROM yourTable T1
INNER JOIN yourTable T2
ON (T1.tag = T2.tag AND T2.item > T1.item)
GROUP BY item1,item2

answered Dec 25, 2013 at 21:50

Tin Tran

6,1763 gold badges25 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to get field count Mysql

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related