w3resource

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.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming