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 query to get the department ID, year, and number of employees joined.

Sample table: employees

Code:

SELECT DEPARTMENT_ID, DATE_FORMAT(HIRE_DATE,'%Y'), 
   COUNT(EMPLOYEE_ID)  
      FROM employees 
        GROUP BY DEPARTMENT_ID, DATE_FORMAT(HIRE_DATE, '%Y') 
           ORDER 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 query to get first name, hire date and experience of the employees.
Next:MySQL string