Results 1 to 8 of 8

Thread: Query to find schools with only boy/girls

  1. #1
    Join Date
    Jun 2007
    Posts
    4

    Query to find schools with only boy/girls

    I've been struggling with this one for quite some time now and would appreciate some help:-

    I'm trying to get a list of schools that only has male students. the closest I have so far:-


    SELECT Schools.SchoolName, FORUM_MEMBERS.M_SEX , COUNT(Schools.SchoolName) AS no_of_students
    FROM Schools
    INNER JOIN FORUM_MEMBERS ON (Schools.SchoolID = FORUM_MEMBERS.SchoolID)
    WHERE (FORUM_MEMBERS.UserType=4)
    GROUP BY Schools.SchoolName, FORUM_MEMBERS.M_SEX
    ORDER BY Schools.SchoolName, FORUM_MEMBERS.M_SEX;

    The result is a list of schools:-
    SchoolName, M_SEX, no_of_students
    school 1, Male, 11
    school 1, Female, 20
    school 2, Male, 8
    school 2, Female, 1
    school 3, Male, 12
    school 4, Male, 14
    school 5, Male, 8
    school 5, Female, 1

    But what I really need is schools with only male students:-
    SchoolName, M_SEX, no_of_students
    school 3, Male, 12
    school 4, Male, 14

    Many Thanks
    Last edited by retrib; 08-16-2007 at 10:54 AM.

  2. #2
    Join Date
    Aug 2007
    Posts
    3
    Perhaps you could use a correlated subquery; join to the FORUM_MEMBERS table twice, once for Male, once for Female. You'll then have School Name, Male Count, and Female Count in a single row. From there, you could only return those where Female = 0.

  3. #3
    Join Date
    Dec 2004
    Posts
    502
    Try this:

    SELECT Schools.SchoolName, FORUM_MEMBERS.M_SEX , COUNT(Schools.SchoolName) AS no_of_students
    FROM Schools
    INNER JOIN FORUM_MEMBERS (ON Schools.SchoolID = FORUM_MEMBERS.SchoolID)
    WHERE (FORUM_MEMBERS.UserType = 4)
    AND NOT EXISTS (SELECT 1 FROM FORUM_MEMBERS AS FORUM_MEMBERS2 WHERE M_SEX = 'Female' AND FORUM_MEMBERS.SchoolID = FORUM_MEMBERS2.SchoolID)
    GROUP BY Schools.SchoolName, FORUM_MEMBERS.M_SEX
    ORDER BY Schools.SchoolName, FORUM_MEMBERS.M_SEX;

  4. #4
    Join Date
    Jun 2007
    Posts
    41
    You are just one step close:
    SELECT Schools.SchoolName, FORUM_MEMBERS.M_SEX , COUNT(Schools.SchoolName) AS no_of_students
    FROM Schools
    INNER JOIN FORUM_MEMBERS ON (Schools.SchoolID = FORUM_MEMBERS.SchoolID)
    WHERE (FORUM_MEMBERS.UserType=4) AND (FORUM_MEMBERS.M_SEX='Male')
    GROUP BY Schools.SchoolName, FORUM_MEMBERS.M_SEX
    ORDER BY Schools.SchoolName, FORUM_MEMBERS.M_SEX;

  5. #5
    Join Date
    Dec 2004
    Posts
    502
    Shamshe, no. The original poster wanted the schools with ONLY male students. Your query brings back ANY schools with male students, including those with both male and female students. Use test data to see this more clearly.

  6. #6
    Join Date
    Jun 2007
    Posts
    41

    Smile

    I see now, apparently didn't read carefully.

    thanks for correcting

  7. #7
    Join Date
    Jun 2007
    Posts
    4
    Thanks all, I've tried the ablove but I've still not sussed it yet though. I think I'm going to have to out source this one.

  8. #8
    Join Date
    Sep 2005
    Posts
    168
    SELECT Schools.SchoolName, tmp.males, tmp.females
    FROM Schools
    JOIN
    --following subquery returns distribution of students per school
    (
    SELECT SchoolID , COUNT(CASE M_SEX WHEN 'Male' THEN 1 END) AS males, COUNT(CASE M_SEX WHEN 'Female' THEN 1 END) AS females
    FROM FORUM_MEMBERS
    GROUP BY SchoolID) tmp ON tmp.SchoolID = Schools.SchoolID
    WHERE tmp.females = 0

    --HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •