SQL - Counting

Posted on 2011-10-07
Medium Priority
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 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 36933323

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
ID: 36933456

great solution as ever.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

807 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