SQL Exercise: Number of employees in each of the department

SQL JOINS on HR Database: Exercise-26 with Solution

26. From the following tables, write a SQL query to find the department name, department ID, and number of employees in each department.

Sample table: departments

Sample table: employees

Sample Solution:

-- Selecting specific columns (d.department_name, e.*) from the 'departments' table, aliased as 'd', and performing a join with a subquery, aliased as 'e'
SELECT d.department_name,
       e.*

-- Joining the 'departments' table (aliased as 'd') with a subquery (aliased as 'e') that calculates the count(employee_id) and department_id grouped by department_id from the 'employees' table
FROM departments d

JOIN
  (SELECT count(employee_id),
          department_id
   FROM employees
   GROUP BY department_id) e USING (department_id);

Sample Output:

department_name		no_of_employees	department_id
Administration		1		10
Marketing		2		20
Purchasing		6		30
Human Resources		1		40
Shipping		45		50
IT			5		60
Public Relations	1		70
Sales			34		80
Executive		3		90
Finance			6		100
Accounting		2		110

Code Explanation:

The said query in SQL which will return a list of department names and employee counts for each department, based on data from the departments and employees tables.
Starting with the department name from the departments table, a subquery selects the number of employees and department IDs for each department. The subquery uses the COUNT aggregate function to count the number of employee IDs for each department, and groups the results by department ID using the GROUP BY clause.
The query then joins the result of the subquery with the departments table on the department ID using the USING clause.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Employees worked without a commission percentage.
Next SQL Exercise: Display the employee with ID and present country.