Select most common value from a field in MySQL

Question

I have a table with a million rows, how do i select the most common(the value which appears most in the table) value from a field?

possible duplicate of [mySQL query to find the most repeated value](http://stackoverflow.com/questions/5581609/mysql-query-to-find-the-most-repeated-value) — Ciro Santilli OurBigBook.com, Sep 07 '15 at 19:31

score 106 · Accepted Answer · answered Oct 07 '11 at 23:00

You need to group by the interesting column and for each value, select the value itself and the number of rows in which it appears.

Then it's a matter of sorting (to put the most common value first) and limiting the results to only one row.

In query form:

SELECT column, COUNT(*) AS magnitude 
FROM table 
GROUP BY column 
ORDER BY magnitude DESC
LIMIT 1

score 26 · Answer 2 · edited May 03 '21 at 16:58

26

This thread should shed some light on your issue.

Basically, use COUNT() with a GROUP BY clause:

SELECT foo, COUNT(foo) AS fooCount 
FROM table
GROUP BY foo
ORDER BY fooCount DESC

And to get only the first result (most common), add

LIMIT 1

To the end of your query.

edited May 03 '21 at 16:58

sMyles

answered Oct 07 '11 at 23:00

Bojangles

score 7 · Answer 3 · answered Jun 01 '18 at 07:56

In case you don't need to return the frequency of the most common value, you could use:

SELECT foo
FROM table
GROUP BY foo
ORDER BY COUNT(foo) DESC
LIMIT 1

This has the additional benefit of only returning one column and therefore working in subqueries.

3 Answers3