This tutorial will show how to find the number of employees under manager.

Create below employee table

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

Insert below data into the above 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',NULL,'MANAGER'),
(7964,'MARTIN','HIKMAN',NULL,'CLERK'),
(7974,'APRIL','HICKMAN',NULL,'SALESMAN');

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

SELECT mgr.emp_id, COUNT(mgr.emp_id)
FROM employee mgr, employee e
WHERE e.emp_mgr_id = mgr.emp_id
GROUP BY mgr.emp_id

Output

emp_id    COUNT(mgr.emp_id)
7698    5
7782    3
7788    1

Thanks for reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit | Email Me

Leave a Reply

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