MySQL Joins Exercises: Get the department name and number of employees in the department

MySQL Joins: Exercise-6 with Solution

Write a query to get the department name and number of employees in the department.

Sample table: employees

Sample table: departments

Code:

SELECT department_name AS 'Department Name', 
COUNT(*) AS 'No of Employees' 
FROM departments 
INNER JOIN employees 
ON employees.department_id = departments.department_id 
GROUP BY departments.department_id, department_name 
ORDER BY department_name;

Sample Output:

Department Name		No of Employees
Accounting			2
Administration			1
Executive			3
Finance				6
Human Resources			1
IT				5
Marketing			2
Public Relations		1
Purchasing			6
Sales				34
Shipping			45

MySQL Code Editor:

Structure of 'hr' database :

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

Previous:Write a query to find the name (first_name, last_name) and hire date of the employees who was hired after 'Jones'.
Next:Write a query to find the employee ID, job title, number of days between ending date and starting date for all jobs in department 90 from job history.