SQL - Counting


2 tables (one to Many) Teacher to students.

Table 1: tblTeacher

Table 2: tblStudents
tblStudents.Gender  (will be "M" or "F")

I require a list as follows;

TeacherName    MaleCOunt     FemaleCount
Joan                        12              10
Paul                         23              22

How can I SQL this ??

Thanks !
Patrick O'DeaAsked:
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

select tblTeacher.TeacherName, sum(iif([tblStudents].[Gender]="M",1,0)) as MaleCount,sum(iif([tblStudents].[Gender]="F",1,0)) as FemaleCount
from tblTeachers T inner join tblStudents S on T.teacherID=S.teacherID
group by tblTeacher.TeacherName

do you know what is the common field on the two tables ?
in the query i used teacherID, change it accordingly
Patrick O'DeaAuthor Commented:

great solution as ever.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.