SQL query for the given table

Question

I have 2 tables, Student and Supervisor:

• STUDENT(supervisorid(pk),name,email....)
• SUPERVISOR(supervisorid(pk),name,email....)

Now I need to print supervisor name, email and the # of students under the supervisor (they will have same supervisor id). Something like:

select supervisorname,
       supervisoremail,
       tot_stud as (select count(*) 
                           Phd_Student s 
                     where s.supervisor_id = r.supervisor_id) 
  from Phd_Supervisor r

Can you please tell me the SQL query for this.

Answer 1

You will want to use the group by clause for this query. You can specify all of the fields that you want to display, as well as the count(*), join the tables, relate the tables , and then put in your group by clause, listing all of the display fields,(without the count(*)), as those are the fields you are grouping the students by to get their count.

Answer 2

select supervisorname,
   supervisoremail,
   (select count(*) 
    from Phd_Student s 
    where s.supervisor_id = r.supervisor_id) as tot_stud
from Phd_Supervisor r