Select product's with the most occurrences

user_8708748 0
Viewed 115

I have the following table in mysql

I want which product_id has been purchased the most for a particular month say aug. So, the query should return product ids and the no of times it has been purchased.I am getting stuck because of the two different product id has been purchased the same no of times, then what will be solution? I have tried this,

select Product_Id, count(Product_Id) repeater   
from mydatabase.dateofpurchase  
where month(dateofpurchase) = 8
group by Product_Id
having count(Product_Id) > 1  
order by repeater desc ;

This Query is returning all the ids and their repetition times. I want only those id's that are maximum times purchased. If it is one then it returns one and if two it returns two.

mysql sql
2 Answers
select Product_Id, count(Product_Id) repeater   
from mydatabase.dateofpurchase  
where month(dateofpurchase) = 8
group by Product_Id
having count(Product_Id) =select max(x) 
                          from (select count(Product_Id) as x    
                                from mydatabase.dateofpurchase  
                                where month(dateofpurchase) = 8
                                group by Product_Id) b
order by repeater desc ;
user_62967590

You can check what is the max repeater , and join it to your original query :

SELECT s.* 
FROM(select Product_Id, count(Product_Id) repeater   
     from mydatabase.dateofpurchase  
     where month(dateofpurchase) = 8
     group by Product_Id) s
JOIN(select count(product_id) as max_repeater
     FROM mydatabase.dateofpurchase  
     where month(dateofpurchase) = 8
     group by Product_Id
     order by max_repeater DESC
     LIMIT 1) t
 ON(t.max_repeater = s.repeater)
user_53537530
Related