SQL Exercise: List the name of the managers with the most employees

SQL subqueries on employee Database: Exercise-45 with Solution

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

45. From the following table, write a SQL query to find those managers who handle maximum number of employees. Return managers name, number of employees.

Sample table: employees

Sample Solution:

SELECT m.emp_name,
       count(*)
FROM employees w,
     employees m
WHERE w.manager_id = m.emp_id
GROUP BY m.emp_name
HAVING count(*) =
  (SELECT MAX (mycount)
   FROM
     (SELECT COUNT(*) mycount
      FROM employees
      GROUP BY manager_id) a);

Sample Output:

 emp_name | count
----------+-------
 BLAZE    |     5
(1 row)

Explanation:

The said query in SQL that retrieves the name of the manager(s) from the employees table who have the highest number of direct reports, along with the number of employees reporting to them.

The query joins the employees table with itself using the manager_id column to match employees with their managers. It then groups the result by the emp_name of the manager and counts the number of employees reporting to each manager.

The HAVING clause filters the result to only include managers whose number of direct reports is equal to the maximum number of direct reports among all managers obtained from a subquery. The subquery that counts the number of employees per manager and returns 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: Departments with more employees than average.
Next SQL Exercise: List managers on lower salaries than their employees.