how can I select most common values in MySQL?

Question

here is my table Table1:

examplecolumn1 | examplecolumn2
--------------------------------
AAA            | 555
BBB            | 555
AAA            | 333
AAA            | 444
CCC            | 111

I'm trying to make a query that returns

AAA 3, 555 2

the most frequent value in column1 and the frequency, and the same for the column 2.
can I do it with a single query in PHP? or should I do more than one query? I have to do it for 4 columns, so if I do it in just one query is better, but I have no idea

I tried:

SELECT COUNT(`examplecolumn1`) AS FREQ FROM `Table1` GROUP BY `examplecolumn1`

thanks in advance

So the result in column 1 may not have any relation to the result in column 2? And what if two (or more) things are equally common? — Strawberry, Dec 24 '15 at 10:01
yes, the result in column 1 don't have any relation to the result in column 2. — Display Name, Dec 24 '15 at 10:06

score 0 · Accepted Answer · answered Dec 24 '15 at 10:02

0

To get the most frequent value in a particular column, do:

SELECT examplecolumn1, COUNT(*) AS count
FROM Table1
GROUP BY examplecolumn1
ORDER BY count DESC
LIMIT 1

If you want this for multiple columns in a single query, just combine them with UNION:

SELECT examplecolumn1, COUNT(*) AS count
FROM Table1
GROUP BY examplecolumn1
ORDER BY count DESC
LIMIT 1
UNION
SELECT examplecolumn2, COUNT(*) AS count
FROM Table1
GROUP BY examplecolumn2
ORDER BY count DESC
LIMIT 1

answered Dec 24 '15 at 10:02

Barmar

741,623
53
500
612

Well, that returns 'one of' the most frequent things – Strawberry Dec 24 '15 at 10:03
If he says he needs to get all the most frequent values, I'll show how to do that more complex query. – Barmar Dec 24 '15 at 10:05

how can I select most common values in MySQL?

1 Answers1