MySQL - SELECT Most occuring value (with several same occurrences)

user_13204155 0
Viewed 18

I have been looking for a way to do a MySQL select to get all the most occuring values. All the solutions i have found where with using LIMIT 1, but this doesn't help if there are more than one value that occur the same amount of times. E.g:

customer

ID FirstName
1 Bob
2 Tom
3 Bob
4 Robert
5 Tom
6 Timothy

The Select for most occuring FirstNames should result in:

FirstName
Bob
Tom

Since both occur twice.

I have tried the following:

SELECT FirstName FROM (
  SELECT FirstName, COUNT(FirstName) as counter FROM customer
  GROUP BY FirstName
  HAVING counter = MAX(counter)
) s

But this doesn't seem to work, i would really appreciate a nudge in the right direction.

select mysql
1 Answers

There can be different ways for doing this

you can try

1st

SELECT FIRSTNAME 
FROM customer
GROUP BY FirstName Having count(FirstName) = (
   SELECT COUNT(FirstName) FROM customer GROUP BY FirstName ORDER BY 1 
   DESC LIMIT 1);

2nd

with cte as
(
  SELECT COUNT(FirstName) MaxCounter
  FROM customer 
  Group By FirstName ORDER BY  COUNT(FirstName) DESC LIMIT 1
)

SELECT c.FirstName
From customer c
Group BY FirstName
HAVING COUNT(FirstName) = (SELECT MaxCounter FROM cte)
user_142290270
Related