MySQL Date and Time Exercises: Query to get the department ID, year, and number of employees joined

MySQL Date Time: Exercise-21 with Solution

Write a MySQL query to get the department ID, year, and number of employees joined.

Sample table: employees

Code:

-- This SQL query calculates the count of employees hired in each department for each year, ordered by department ID.

SELECT 
    DEPARTMENT_ID, -- Selecting the 'DEPARTMENT_ID' column from the 'employees' table.
    DATE_FORMAT(HIRE_DATE,'%Y'), -- Formatting the hire date to extract the year and selecting it as 'YEAR'.
COUNT(EMPLOYEE_ID) -- Counting the number of employees in each department for each year.
FROM 
employees -- Specifying the 'employees' table.
GROUP BY 
    DEPARTMENT_ID, DATE_FORMAT(HIRE_DATE, '%Y') -- Grouping the result set by department ID and year of hire date.
ORDER BY 
    DEPARTMENT_ID; -- Ordering the result set by department ID.

Explanation:

• This SQL query calculates the count of employees hired in each department for each year, ordered by department ID.
• It selects the department ID, extracts the year from the hire date, and counts the number of employees for each department and year combination.
• The result set is grouped by department ID and year of hire date using the GROUP BY clause.
• The ORDER BY clause ensures that the result set is ordered by department ID.

Sample Output:

DEPARTMENT_ID	DATE_FORMAT(HIRE_DATE,'%Y')	COUNT(EMPLOYEE_ID)
0		1987				1
10		1987				1
20		1987				2
30		1987				6
40		1987				1
50		1987				45
60		1987				5
70		1987				1
80		1987				34
90		1987				3
100		1987				6
110		1987				2

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 first name, hire date and experience of the employees.
Next:MySQL string