Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Apr 2007
    Posts
    123
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Count users by gender

    I have two tables Users and Changes
    Users
    Username, Address, County, MemberType, Gender

    Changes
    Filename, Username

    the query is to count the number of male and female users in various counties who make changes to certain files.
    What I have so far is,
    Code:
    select distinct filename, county, sum(if(C.Username=U.Username and U.Gender='Male',1,0) as Male, sum(if(C.Username=U.Username and U.Gender='Female',1,0) as Female from Changes as C inner join Users as U where U.County='Durham' order by Filename
    Problem is I have 1 user who has has changed file 1 twice and file 2 once, user is male.
    the query result will look like this
    filename Male Female County
    1 1 0 Durham
    2 1 0 Durham

    The actual result I'm getting is
    filename Male Female County
    1 2 0 Durham

    I don't know why is not selecting the second file. Its also counting file 1 as being changed by two users not 1.
    Can anyone help with this?

  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    this is because you are select multiple DISTINCT fields,

    PHP Code:
    select distinct filenamecounty
    sum(if(C.Username=U.Username and U.Gender='Male',1,0) as Male
    sum(if(C.Username=U.Username and U.Gender='Female',1,0) as Female 
    try using a "GROUP BY" for 'country' instead like this;

    PHP Code:
    SELECT DISTINCT(filename,  
    sum(if(C.Username=U.Username and U.Gender='Male',1,0) As Male), 
    sum(if(C.Username=U.Username and U.Gender='Female',1,0) As Female), county 
    FROM Changes 
    As C INNER JOIN Users As U WHERE U.County='Durham' GROUP BY country ORDER BY Filename 
    Last edited by angst; 05-24-2011 at 09:43 PM.

  • #3
    Regular Coder
    Join Date
    Apr 2007
    Posts
    123
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Your suggested solution gives a syntax error at 'As Male'

  • #4
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    updated the snippet above.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    You do not need or want to use DISTINCT at all.

    You want to only use GROUP BY. But you need to GROUP BY *ALL* of the SELECTed fields that are *not* used in aggregate functions (that is, in this case, the SUM functions).

    On top of that, your INNER JOIN was wrong, because you had no ON condition for the JOIN.

    I have simplified this to an "implicit" join (another style of inner join), thus:
    Code:
    SELECT filename, county,  
          sum(if(U.Gender='Male',1,0) As Males, 
          sum(if(U.Gender='Female',1,0) As Females 
    FROM Changes As C, Users As U 
    WHERE C.Username=U.Username 
    AND U.County='Durham' 
    GROUP BY filename, county
    ORDER BY filename, county
    The part of the code in red is optional. If omitted, you will get a list of all filename and all county values, with Male/Female counts for each. If present, then of course you will get only the one county.

    Not to ask a silly question, but why does a field named "fileNAME" contain only numbers??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Apr 2007
    Posts
    123
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Cheers guys, its working now. Well if you've never heard of files have numbers as names, you have now!


  •  

    Posting Permissions

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