sql - MySQL from subquery returns wrong value

Question

I'm a bit new to MySQL and I encountered a problem in this statement:

select dat.month, max(dat.emp) 
from ( select month(hire_Date) as month, count(*) as emp
       from employees
       group by month ) as dat ;

The statement supposed to return the month with the highest amount of employees. The statement returns the right number but the field month is wrong, it seems like it returns the value form the first record no matter what.

What am I missing? How do I solve this issue? Thank you.

question from:https://stackoverflow.com/questions/65943499/mysql-from-subquery-returns-wrong-value

Answer 1

If you want the calendar month, combining data from multiple years, then you are on the right track. But I would suggest order by and limit:

select month(hire_Date) as month, count(*) as emp
from employees
group by month
order by count(*) desc
limit 1;