SQL Exercise: List departments with the most employees

SQL subqueries on employee Database: Exercise-42 with Solution

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

42. From the following tables, write a SQL query to find those departments where the highest number of employees works. Return department name.

Sample table: employees

Sample table: department

Sample Solution:

SELECT dep_name
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_name
-----------
 MARKETING
(1 row)

Explanation:

The said query in SQL that selects the department name(s) from the departments table where the number of employees in the department is equal to the maximum number of employees in any department.

The WHERE clause filters the results to only include departments where the department ID is included in the subquery.

The subquery selects the department ID from the employees table and groups the results by department ID.

The HAVING clause filters the results to only include departments where the count of employees in that department is equal to the maximum count of employees across all departments.

The subquery inside the HAVING clause calculates the maximum count of employees across all departments by selecting the count of employees for each department, grouping the results by department ID, and selecting the maximum count.

Practice Online

Structure of employee Database:

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

Previous SQL Exercise: Number of employees equals to department length.
Next SQL Exercise: Employees who joined in the company on the same date.