CODEWITHSUNDEEP
mysql
Prakash Bhavnath
Prakash Bhavnath

Reputation: 279

Query to count employees for each manger(also zero count for manager)

Manager table :

create table Manager(id integer, name varchar(100));
insert into Manager(id, name) values(1, "M1");
insert into Manager(id, name) values(2, "M2");
insert into Manager(id, name) values(3, "M3");

Employee table along with manager id(m_id)

create table Employee(id integer, m_id integer, name varchar(100));
insert into Employee(id, m_id, name) values(11, 1,"E1");
insert into Employee(id, m_id, name) values(12, 1,"E2");
insert into Employee(id, m_id, name) values(13, 2,"E3");

Executed query:

select M.id, M.name, count(E.id) from Manager as M, Employee as E where E.m_id=M.id group by E.m_id, M.id, M.name;

Output:

1   M1  2
2   M2  1

Expected output: 

1   M1  2
2   M2  1
3   M3  0

Please help me how to include M3 manager along with 0 employee assigned to him?

Upvotes: 0

Views: 46

Answers (1)

CloudPotato
CloudPotato

Reputation: 1275

You should use a left join

SELECT M.id, M.name, count(E.id) from Manager as M LEFT JOIN Employee as E ON E.m_id=M.id GROUP BY E.m_id, M.id, M.name;

Upvotes: 2

Related Questions