mysql multiple COUNT with multiple columns

Question

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?

Alex · Accepted Answer · 2016-05-09 14:40:47Z

3

http://sqlfiddle.com/#!9/f39ce/2

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

and here is an example of grouping query, if you ever need one:

http://sqlfiddle.com/#!9/bdd738/5

SELECT t1.name,
  SUM(IF(t1.name=t2.Supervisor,1,0)),
  SUM(IF(t1.name=t2.manager,1,0))
FROM mytable t1
LEFT JOIN mytable t2
ON t1.name = t2.Supervisor
  OR t1.name = t2.manager
GROUP BY t1.name;

edited May 9, 2016 at 14:40

answered May 9, 2016 at 14:01

Alex

17.3k1 gold badge31 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3431239 Over a year ago

thank you for the answer, very simple, i like it! may i know what is the meaning of '1,0' inside that query ?

Alex Over a year ago

since we use SUM function in query we need to summarize something :-) so when supervisor or manager equal to 'David' we increasesum +1 and when it is not equal we adding nothing +0

Tudor Constantin · Accepted Answer · 2016-05-09 13:58:37Z

2

It can:

select Name as BossName,
sum( case when Supervisor = Name then 1 else 0 end) as Supervises,
sum( case when Manager = Name then 1 else 0 end) as Manages
 from your_table group by Name;

This will get you something like:

BossName Supervises    Manages

David    2             3
Josseph  3             0
Jenny    0             0
....     0             0

answered May 9, 2016 at 13:58

Tudor Constantin

26.9k7 gold badges54 silver badges73 bronze badges

1 Comment

Alex Over a year ago

don't want to downvote. but you query is not working at all. don't know who and why upvoted. sqlfiddle.com/#!9/bdd738/1

Andrews B Anthony · Accepted Answer · 2016-05-09 14:03:09Z

1

Use the below query

select name,(select count(*) from <table> where supervisor=name) as supervisor,
(select count(*) from <table> where manager=name) as manager    
from <table>

answered May 9, 2016 at 14:03

Andrews B Anthony

1,38110 silver badges28 bronze badges

Collectives™ on Stack Overflow

mysql multiple COUNT with multiple columns

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related