SQL - Counting

Posted on 2011-10-07
Last Modified: 2012-05-12

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 !
Question by:Patrick O'Dea
    LVL 119

    Accepted Solution


    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

    Author Closing Comment

    by:Patrick O'Dea

    great solution as ever.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now