Clever Geek Handbook

Sql Query: match column values

I have a table like this:

col1     col2
id1      item1
id1      item2
id1      item3
id2      item1
id2      item4
id3      item2
id3      item3

I need to run a query on this table to find the number of times each pair items

is shared by the common id

. For example, in the above case, the pair (item1, item2)

has a counter 1

(only id1 has both item1 and item2). Likewise, the pair (item2, item3)

has a counter 2

(id1, id3).

I can write code to achieve this, but I cannot come up with an sql query. Help me write an efficient query to output the following:

col1    col2    count   
item1   item2    1
item1   item3    1
item1   item4    1
item2   item3    2

thank

+3


source to share


2 answers 


select    t1.col2  as item_A
         ,t2.col2  as item_B
         ,count(*) as cnt

from                mytable t1 
          join      mytable t2
          on        t1.col1 = t2.col1

where     t1.col2 < t2.col2

group by  t1.col2
         ,t2.col2

      

        
        
        
      

    


+--------+--------+-----+
| item_a | item_b | cnt |
+--------+--------+-----+
| item1  | item2  |   1 |
| item1  | item3  |   1 |
| item1  | item4  |   1 |
| item2  | item3  |   2 |
+--------+--------+-----+
+2


source


You can do this with self-join:



select t1.col2, t2.col2, count(*)
from t t1 join
     t t2
     on t1.col1 = t2.col1
where t1.col2 < t2.col2
group by t1.col2, t2.col2;
+2


source






More articles:


All Articles