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:

-- This SQL query retrieves the department name along with the count of employees in each department, ordered alphabetically by department name.

SELECT department_name AS "Department Name", -- Selects the department_name column and labels it as "Department Name"
COUNT(*) AS "No of Employees" -- Calculates the count of employees in each department and labels it as "No of Employees"
FROM departments -- Specifies the first table from which to retrieve data, in this case, the departments table
INNER JOIN employees -- Performs an inner join between the departments and employees tables
ON employees.department_id = departments.department_id -- Specifies the join condition based on the department_id column
GROUP BY departments.department_id, department_name -- Groups the results by department_id and department_name
ORDER BY department_name; -- Orders the results alphabetically by department_name

Explanation:

• This SQL query retrieves the department name along with the count of employees in each department, ordered alphabetically by department name.
• The SELECT statement selects the department_name column from the departments table and calculates the count of employees in each department, labeling them as "Department Name" and "No of Employees" respectively.
• The FROM clause specifies the first table from which to retrieve data, which is the departments table.
• An INNER JOIN operation is performed between the departments and employees tables based on the common column department_id.
• The ON clause specifies the join condition where the department_id in the employees table matches the department_id in the departments table.
• The GROUP BY clause groups the results by department_id and department_name.
• The ORDER BY clause orders the results alphabetically 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.