3

I'm pretty new to SQL (I'm using MySQL) and need some help. I'm currently trying to select the most common age(s) from a table called PERSON. Suppose PERSON has an AGE column which has values: 10, 10, 20, 20, 30. The query should return the values 10 and 20.

The following query only retrieves the top row (20):

SELECT AGE FROM PERSON GROUP BY AGE ORDER BY COUNT(*) DESC LIMIT 1;

My other thought was to try something like:

SELECT AGE FROM PERSON GROUP BY AGE HAVING COUNT(AGE) = MAX(COUNT(AGE));

This returns an error, stating that it is invalid use of group function.

Any help would be greatly appreciated. Thanks!

lots_of_questions
  • 1,109
  • 3
  • 16
  • 24

3 Answers3

5

This will do:

select age from persons
group by age
having count(*) = (
  select count(*) from persons
  group by age
  order by count(*) desc
  limit 1)
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
4
SELECT *, COUNT(AGE) as age_count
FROM PERSON
GROUP BY AGE
ORDER BY age_count DESC
LIMIT 1

Can't test it here but it should work.

Castilho
  • 3,147
  • 16
  • 15
0
WITH x AS (
  SELECT age, COUNT(*) numOfAge
    FROM person
    GROUP BY age
)
SELECT age
  FROM x
  WHERE numOfAge = ( SELECT MAX(numOfAge) FROM x)
  ORDER BY age
Glenn
  • 8,932
  • 2
  • 41
  • 54