MySQL Date and Time Exercises: Query to get the years in which more than 10 employees joined

MySQL Date Time: Exercise-16 with Solution

Write a MySQL query to get the years in which more than 10 employees joined.

Sample table : employees

Code:

-- This SQL query retrieves the year part of the hire date for employees and groups them by year, filtering only those years where the count of employees hired is greater than 10.

SELECT 
    DATE_FORMAT(HIRE_DATE,'%Y') -- Formats the hire date to extract the year part and returns it as 'YYYY'.
FROM 
employees -- Specifies the 'employees' table.
GROUP BY 
    DATE_FORMAT(HIRE_DATE,'%Y') -- Groups the result set by the year part of the hire date.
HAVING 
COUNT(EMPLOYEE_ID) > 10; -- Filters the grouped results to include only those years where the count of employees is greater than 10.

Explanation:

• This SQL query selects the year part of the hire date for employees using the DATE_FORMAT() function.
• The result set is then grouped by the year part of the hire date to count the number of employees hired in each year.
• The HAVING clause filters the grouped results to include only those years where the count of employees is greater than 10.
• The query returns the years where more than 10 employees were hired.

Sample Output:

DATE_FORMAT(HIRE_DATE,'%Y')
1987

Pictorial Presentation of the above query:

MySQL Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a MySQL query to get the firstname, lastname who joined in the month of June.
Next:Write a MySQL query to get first name of employees who joined in 1987.