user3431239 user3431239 - 9 months ago 38
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?


SELECT SUM(IF(Supervisor='David',1,0)),
FROM mytable