Getting Sum of People Belonging to Record in Same Table

[jerijeri]

Hi,

I have a table structured (simplified for example) like this:

id: Name : Trainer_id

1:John Doe:
2:Jane Doe:
3:Linus T:
4:Charlie B:1
5:Lucy L:1
6:Bob R:3
7:Jimmy B:
8:Suzie Q:1

I want to create a Query that will output the following:

Salesperson ID : Name : # of Trainees
1 : John Doe : 3
2 : Jane Doe : 0
3 : Linus T : 1
4 : Charlie B : 0

etc.

I'm not sure of the best way to do this.

Thanks.

Jer

 

[r937]

[tt]select A.id as Salesperson
, A.name
, count(B.trainer_id) as Trainees
from yourtable A
left outer
join yourtable B
on B.trainer_id = A.id[/tt]

 

[r937]

aaak, i forgot the GROUP BY

[tt]select A.id as Salesperson
, A.name
, count(B.trainer_id) as Trainees
from yourtable A
left outer
join yourtable B
on B.trainer_id = A.id
group
by A.id
, A.name[/tt]

rudy

http://rudy.ca/

 

[jerijeri]

Thank you for your response. I found surprising little documentation on self-joins. Your answer allowed me to solve this problem.

Thanks again.

Jer