user3422427 user3422427 - 5 months ago 13
MySQL Question

mysql - How to calculate mode several times in a table

Im trying to get the mode (the value that appears most often in a set of data) of price for each store in the following table

create table t_products (
store_id int,
product varchar(20),
price int
)


I already have this query which retrieves all the ocurrences of each price in each store

SELECT store_id, price, count(price)
FROM t_products
GROUP BY store_id, price
ORDER BY count(price) DESC;


What else is missing? I was trying to use max() function to get the highest price for each store in several ways with no success.

Thanks in advance

PD this is the result of my current query.

store_id|price|count(price)
2 40 5
1 70 5
2 90 4
3 60 2
1 60 1
3 50 1
3 80 1
1 50 1


I only want to keep

store price
2 40
1 70
3 60

Answer
select tmp.store_id, tmp.price from (
    SELECT store_id, price
    FROM t_products
    GROUP BY store_id, price
    ORDER BY count(price) DESC
) as tmp
group by tmp.store_id

Good luck )