Simple sql query, but stuck on it

Question

database mode

The only relevant table is 'employee' in the database model.

Asked: In which month are the most employee's birthdays?

By using

SELECT DATEPART(m, dateofbirth) AS month
FROM employee

I can actually see all the months for every employee and count it myself.

But how can I show the most common birthday month?

Thanks in advance!

recent output (for comment below)

Answer 1

This would give you exact month you're looking for:

SELECT TOP 1 DATEPART(m, dateofbirth) AS month
FROM employee
GROUP BY DATEPART(m, dateofbirth)
ORDER BY count(DATEPART(m, dateofbirth)) DESC

Answer 2

You need to use GROUP BY. This groups up the separate month values. Once you've done that, you can apply COUNT, and then order the values in descending order on that statistic. Then you need to wrap that logic in a Common Table Expression, so you can select just the months that have the maximum COUNT.

WITH ranking AS (
    SELECT 
    DATEPART(m, dateofbirth) AS month,
    COUNT(*) as ct
    FROM DM_MTA.dbo.employee
    GROUP BY DATEPART(m, dateofbirth) 
) 
select
month 
from
ranking
where ct = (select max(ct) from ranking)