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,
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?

this is because you are select multiple DISTINCT fields,


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


try using a "GROUP BY" for 'country' instead like this;


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

Your suggested solution gives a syntax error at 'As Male'

updated the snippet above.

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:

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??

Cheers guys, its working now. Well if you've never heard of files have numbers as names, you have now!