user3431239 user3431239 - 2 years ago 85
SQL Question

mysql multiple COUNT with multiple columns

i've designed a organization table as per below:

Name Supervisor Manager
David - -
Josseph David -
Jenny Josseph David
Step Josseph David
Mike Josseph David
steven David

The Chart above explain that, David is at the highest position of the organization, Josseph and steven are report to David; Jenny, step and mike reporting to Josseph.

I would like to know how many person are reporting to David; and David is the manager to how many person? This is my expected table:

Supervisor Manager
2 3

Can it be done with only 1 query?

Answer Source!9/f39ce/2

SELECT SUM(IF(Supervisor='David',1,0)),
FROM mytable
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download