SQL JOINS on HR Database: Display the department name, department ID, and 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:

SELECT d.department_name,
       e.*
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

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous: From the following tables, write a SQL query to find full name (first and last name), job title, starting and ending date of last jobs of employees who worked without a commission percentage.
Next: From the following tables, write a SQL query to find the full name (first and last name) of the employee with ID and name of the country presently where he/she is working.