Using Count in sql

Question

I need help writing a Sql command. I am pretty new at sql so everything is a little confusing. The problem: For each instructor list his / her name and the number of students he / she mentors

There are three tables:

Person with the Names and ID
Instructor with Instructor ID which references ID
Student with StudentID and MentorID which references ID

I tried:

select distinct p.Name,count(d.MentorID) 
from Person p, Instructor e, Student d
where e.InstructorID = d.MentorID   
and p.ID = e.InstructorID;

But that only gives me one results instead a count per instructor.

Use GROUP and HAVING

Andrew Svetlov
– Andrew Svetlov

2015-02-05 06:46:58 +00:00
Commented Feb 5, 2015 at 6:46 — Andrew Svetlov
– Andrew Svetlov, Commented Feb 5, 2015 at 6:46

juergen d · Accepted Answer · 2015-02-05 06:43:35Z

1

Build a group and then count() counts for every group and not the complete result.

select p.Name, count(d.MentorID) 
from Person p
join Instructor e on p.ID = e.InstructorID
join Student d on e.InstructorID = d.MentorID  
group by p.Name

answered Feb 5, 2015 at 6:43

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

Nubbins Over a year ago

Thank you! That worked beautifully and good explanation also!

Collectives™ on Stack Overflow

Using Count in sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related