Counting and Grouping Records based on Gender

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting and Grouping Records based on Gender

Author

Topic

pejeguerrero
Starting Member

2 Posts

Posted - 2010-01-07 : 13:56:12

Hello everyone,
I'm having trouble writing a query to return a count of students by gender and how many are taking specific courses (Lessons/Classes).

These are the tables that I'm using to pull the information from ...

Table Courses
-------------------------------
CourseID,StudentID,CourseTypeID
1001,1001,1
1002,1002,1
1003,1002,2
1004,1003,1
1005,1001,2

Table Students
-------------------------------
StudentID,Name,GenderID
1001,John Smith,1
1002,Maria Perez,0
1003,Marcus Dell,1

Table Course Types
-------------------------------
CourseTypeID,Name
1,Course #1
2,Course #2
3,Course #3
4,Course #4

Can someone show me the light on how to return the following result?

Course Name,Male,Female,Total
Course #1,2,1,3
Course #2,1,1,2

Thanks in advance ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 14:00:14

[code]SELECT ct.Name,
SUM(CASE WHEN s.GenderID=1 THEN 1 ELSE 0 END) AS Male,
SUM(CASE WHEN s.GenderID=0 THEN 1 ELSE 0 END) AS Female,
COUNT(StudentID) AS Total
FROM CourseType ct
INNER JOIN Courses c
ON c.CourseTypeID=ct.CourseTypeID
INNER JOIN Students s
ON s.StudentID=c.StudentID
GROUP BY ct.Name
[/code]

pejeguerrero
Starting Member

2 Posts

Posted - 2010-01-07 : 14:39:21

visakh16 your query worked like a charm. Thank you for your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 02:16:46

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources