sql - How to count how many times that each pair of item has same order id in mySQL -

sql - How to count how many times that each pair of item has same order id in mySQL -

i want count how many times each pair of sku have been ordered together(have same orderid) have order table of customers has 2 column orderid , skuid illustration

orderid skuid 1 1 1 2 1 3 2 1 2 2 2 4 3 1 3 4

i want result table as

sku1 sku2 count 1 2 2 1 3 1 1 4 2 2 3 1 2 4 1 3 4 0

thank you

you can self-join , aggregation:

select o1.sku, o2.sku, count(distinct o1.orderid) numorders orders o1 bring together orders o2 on o1.orderid = o2.orderid , o1.sku < o2.sku;

if know skus unique in each order, can utilize count(*) instead of count(distinct).

mysql sql