Mysql Question posted in
The official MySQL documentation can be found here.

How can I find out the most common combination of a column in MYSQL

I have a table of Client and Product(that the client purchased)

Client Product
Alex A
Alex B
Alex C
Alex D
Peter A
Peter B
Peter C
Aline C
Aline D
Aline E
Aline F
Aline G
Joao B
Joao C
Joao D
Joao E
Nikky A
Nikky B
Nikky C

I want to know the most common combination of 3 products that the client purchased, like this table, the final result will be "A,B,C"
Since it happened 3 times (Alex, Peter, Nikky)

Tags:

2

Answers


  1. 0

    Assuming the sample data you provided is from the Client_Product table. Here’s how you can achieve this.

    SELECT CONCAT_WS(',', t.Product1, t.Product2, t.Product3) AS ProductCombination, COUNT(*) AS Count
FROM (
    SELECT t1.Client, t1.Product AS Product1, t2.Product AS Product2, t3.Product AS Product3
    FROM Client_Product t1
    JOIN your_table t2 ON t1.Client = t2.Client AND t1.Product < t2.Product
    JOIN your_table t3 ON t2.Client = t3.Client AND t2.Product < t3.Product
) t
GROUP BY ProductCombination
ORDER BY Count DESC
LIMIT 1;

    PS: feel free to rename the table name according by replacing Client_Product if it is different from your table name.

    Login or Signup to reply.
  2. 0

    This is just a "finished" version of Selaka’s answer.

    SELECT CONCAT_WS(',', cp1.Product, cp2.Product, cp3.Product) AS ProductCombo, COUNT(*) AS Cnt
FROM Client_Product cp1
JOIN Client_Product cp2 ON cp1.Client = cp2.Client AND cp1.Product < cp2.Product
JOIN Client_Product cp3 ON cp2.Client = cp3.Client AND cp2.Product < cp3.Product
GROUP BY ProductCombo
ORDER BY Cnt DESC
LIMIT 1;

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search