PostgreSQL JOINS: Make a join with tables employees and departments to get the department name and number of employees working in each department

6. Write a query to make a join with two tables employees and departments to get the department name and number of employees working in each department.

Sample Solution:

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 table: employees

Sample table: departments

Output:

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

 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            |              33
 Shipping         |              45
(11 rows)

Practice Online

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

Previous: Write a query to make a join with a table employees and itself to find the name, including first_name and last_name and hire date for those employees who were hired after the employee Jones.
Next: Write a query to make a join to find the employee ID, job title and number of days an employee worked, for all the employees who worked in a department which ID is 90.