Hardeep Singh Negi Hardeep Singh Negi - 1 year ago 106
MySQL Question

Select product's with the most occurrences

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.

Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download