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:
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
What is the difficulty level of this exercise?
Based on 1 votes, average difficulty level of this exercise is Hard
.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics