Find managers and how many employees they manage

Published: May 12, 2017

---

create table employee (emp_id int, emp_name varchar(225), mrg_id int);

insert into employee values (1, "A", null);
insert into employee values (2, "B", null);
insert into employee values (3, "C", null);
insert into employee values (4, "D", null);
insert into employee values (5, "E", null);
insert into employee values (6, "F", 13);
insert into employee values (7, "G", 13);
insert into employee values (8, "H", 13);
insert into employee values (9, "I", null);
insert into employee values (10, "J", 1);
insert into employee values (11, "K", 1);
insert into employee values (12, "L", 3);
insert into employee values (13, "M", 14);
insert into employee values (14, "N", null);
insert into employee values (15, "O", 14);
insert into employee values (16, "P", 16);

-- v1 with in
select e.emp_name, (select count(*) from employee where mrg_id = e.emp_id) as emp_manage from employee e where e.emp_id in (select emp.mrg_id from employee emp where emp.mrg_id is not null group by mrg_id);

-- v2 with join
select manager.emp_id,
 manager.emp_name,
 (select count(*) from employee where mrg_id = manager.emp_id) as manages_emp
 from employee manager
 join employee e
 on e.mrg_id = manager.emp_id and e.mrg_id is not null group by manager.emp_id;