MySQL query to find number of employees under manager

In this tutorial I am going to show you how to find the number of employees under manager. Generally employees and managers both are employees of an organization. But according to the experience level seniority some employees in the organization become manager, lead, etc. Therefore the junior employees are going to report the senior employees.

This example will show you how to find those senior employees, mainly managers, and how many junior employees are there under each manager.

I am using here MySQL database server to build the SQL statement to find out the desired results. You may use any database and the underlying concept is same.

Prerequisites

MySQL 8.0.17

MySQL Table

For this example, the first thing would be to create the table in MySQL server. In your real application the table may already exist in the database server.

The table employee is created using the following DDL command under roytuts database.

CREATE TABLE `employee` (
  `emp_id` int 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 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;

To test the query you need some data into the above table. So 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');

Find Employees under Manager

Now I am going to create the following query to find out employees working under each manager.

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

Testing the SQL

Now executing the above query you will see the following output in your database console.

mysql query to find number of employees under each manager

Source Code

Download

Thanks for reading.

Roy Tutorials