MySQL query to find managers who are having minimum number of employees under them

This tutorial will show how to find the managers who are having specified minimum number of employees working under them.

Prerequisites

MySQL 8.0.17

Creating Table in MySQL

Create below employee table in MySQL server under database roytuts.

CREATE TABLE `employee` (
  `emp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_first_name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `emp_last_name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `emp_mgr_id` int(11) DEFAULT NULL,
  `emp_designation` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Dumping Data into Table

Insert below data into the employee table.

insert  into `employee`(`emp_id`,`emp_first_name`,`emp_last_name`,`emp_mgr_id`,`emp_designation`)
values
(7369,'SMITH','JHON',7902,'CLERK'),
(7499,'ALLEN','BORDER',7698,'SALESMAN'),
(7521,'WARD','SPACE',7698,'SALESMAN'),
(7654,'MARTIN','FOWLER',7698,'SALESMAN'),
(7698,'BLAKE','RAY',NULL,'MANAGER'),
(7782,'CLARK','MICHAEL',NULL,'MANAGER'),
(7788,'SCOTT','TIGER',7566,'ANALYST'),
(7839,'KING','ROY',NULL,'VICE PRESIDENT'),
(7844,'TURNER','RICK',7698,'SALESMAN'),
(7876,'ADAMS','EVE',7788,'CLERK'),
(7900,'JAMES','BOND',7698,'CLERK'),
(7902,'FORD','LAMBDA',7566,'ANALYST'),
(7934,'MILLER','JOHN',7782,'CLERK'),
(7954,'FRANK','JOHN',7782,'MANAGER'),
(7964,'MARTIN','HIKMAN',NULL,'CLERK'),
(7974,'APRIL','HICKMAN',7782,'SALESMAN');

Building Query

Build below query to find out minimum two employees working under each manager.

SELECT mgr.emp_id manager_id, mgr.emp_first_name manager_first_name, mgr.emp_last_name manager_last_name
FROM employee mgr, employee e
WHERE e.emp_mgr_id = mgr.emp_id
GROUP BY mgr.emp_id
HAVING COUNT(e.emp_id) > 2;

Output

Execute the above query, you will get below output:

manager_id manager_first_name manager_last_name
7698	         BLAKE	             RAY
7782	         CLARK	           MICHAEL

Thanks for reading.

Leave a Reply

Your email address will not be published. Required fields are marked *