mySQL query to find the most repeated value

Question

I have a table with several rows, for each of them I need to know the most common value.

Example:

row_1 has

car
boat
car
car
truck
truck
plane
car
car

as its values.

I need to know what is the most common value (in this case car). I have several ideas, but since I must do this for the 30 rows I would like an easy and not CPU intensive query.

possible duplicate of [Find most frequent value in SQL column](http://stackoverflow.com/questions/12235595/find-most-frequent-value-in-sql-column) — Ciro Santilli OurBigBook.com, Sep 07 '15 at 19:32

score 26 · Accepted Answer · answered Apr 07 '11 at 13:13

To get a list of values and the number of their appearances:

select col_name, count(col_name) c from table
group by col_name
order by c desc;

If you want only the most common value:

select col_name, count(col_name) c from table
group by col_name
order by c desc
limit 1;

score 4 · Answer 2 · answered Apr 07 '11 at 13:14

I wrote the query in a way such that if there are more items with the same highest number of occurrences, you will see them all, not just one of them.

select item
from table
group by item
having count(item) = (
select count(item) as great
from table
group by item order by great desc limit 1)

mySQL query to find the most repeated value

2 Answers2

Linked

Related