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

MYSQL GROUP BY most frequent value for each ID

I have following 2 tables: Sales and Menu.
SALES TABLE

customer_id product_id
A 1
A 2
A 2
A 3
A 3
A 3
B 1
B 1
B 3
B 3
B 2
B 2
C 3
C 3
C 3

MENU TABLE

product_id product_name
1 sushi
2 curry
3 ramen

**My problem statement is : **
Which item was the most popular for each customer.

So I am expecting the following output.

customer_id product_name
A ramen
B sushi
C ramen

I have tried the following query :

SELECT s.customer_id, m.product_name,
       (SELECT count(s.product_id) as freq
        from sales
        GROUP BY product_id
        ORDER BY freq DESC
        LIMIT 1) as maxFreq
from sales s
INNER JOIN menu m
  ON s.product_id = m.product_id
GROUP BY s.customer_id, s.product_id;

I can not apply group by on maxfreq because it is an inner query. Otherwise my work would be done.

Tags:

2

Answers


  1. 0

    You may use a ranking query to rank products for each customer using the ROW_NUMBER() function then extract rank=1 from the result to achieve the desired result. With this query you may also find rank 2 etc. by updating 1 to 2 etc.

    SELECT 
    customer_id, 
    product_name
FROM (
    SELECT 
        s.customer_id, 
        m.product_name,
        COUNT(s.product_id) AS freq,
        ROW_NUMBER() OVER (PARTITION BY s.customer_id ORDER BY COUNT(s.product_id) DESC) AS rank
    FROM sales s
    INNER JOIN menu m 
    ON s.product_id = m.product_id
    GROUP BY s.customer_id, s.product_id
) AS ranked
WHERE rank = 1;
    Login or Signup to reply.
  2. 0

    Using simple max and sum

    select
       max(c.cnt) freq
       ,p.product_name
       from ( select count(product_id) cnt ,product_id from customer
       group by customer_id , product_id ) c
       inner join product p on p.product_id=c.product_id
group by p.product_name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search