SQL Exercise: Departments where maximum number of employees work

SQL subqueries on employee Database: Exercise-29 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

29. From the following table, write a SQL query to find those departments where maximum number of employees work. Return department ID, department name, location and number of employees.

Sample table: employees

Sample table: department

Sample Solution:

SELECT d.dep_id,
       d.dep_name,
       d.dep_location,
       count(*)
FROM employees e,
     department d
WHERE e.dep_id = d.dep_id
GROUP BY d.dep_id
HAVING count(*) =
  (SELECT MAX (mycount)
   FROM
     (SELECT COUNT(*) mycount
      FROM employees
      GROUP BY dep_id) a);

OR

SELECT *
FROM department
WHERE dep_id IN
    (SELECT dep_id
     FROM employees
     GROUP BY dep_id
     HAVING count(*) IN
       (SELECT MAX (mycount)
        FROM
          (SELECT COUNT(*) mycount
           FROM employees
           GROUP BY dep_id) a));

Sample Output:

 dep_id | dep_name  | dep_location | count
--------+-----------+--------------+-------
   3001 | MARKETING | PERTH        |     6
(1 row)

Explanation:

The said query in SQL that retrieves information about departments that is department ID, name, location, and the number of employees in each department and their employee counts.

The query filters the result to only include departments where the number of employees is equal to the maximum number of employees across all departments.

The query joins the 'employees' and 'department' tables based on the "dep_id" column. It then groups the result by department ID, and computes the count of employees in each group using the count(*) function.

The "HAVING" clause filters the result to include only those departments where the employee count matches the maximum employee count across all departments obtained from a subquery.

The subquery that first groups all employees by department and computes the count, and then selects the maximum count using the "MAX" function.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Employees in department 1001, salary higher than 2001.
Next SQL Exercise: Display the employees whose manager name is JONAS.