SQL SUM and CASE and DISTINCT

I've been trying to re-do this statement but to no avail.

SELECT b.Program AS Program,
    SUM(CASE WHEN a.Gender LIKE 'M%' THEN 1 ELSE NULL END) AS Males,    
    SUM(CASE WHEN a.Gender LIKE 'F%' THEN 1 ELSE NULL END) AS Females,
    SUM(CASE WHEN e.Activity LIKE 'Arts' THEN 1 ELSE NULL END) AS Arts,
    AVG(CASE WHEN a.Gender LIKE 'M%' THEN CAST(f.Score AS DEC(10,2)) ELSE NULL END)
AS MalesAverage
FROM tblChildren a
    LEFT JOIN tblInvolvement b ON b.ChildID = a.ChildID
    LEFT JOIN tblActivities e ON e.ChildID = b.ChildID
    LEFT JOIN tblScores f ON f.ChildID = b.ChildID
WHERE b.Place = 'Location'
    AND b.Program = 'Program'
    AND b.Year = '2009-10'
    AND f.Assessment LIKE '%Pre%Assessment%'
 开发者_开发技巧   AND e.StudentID = b.StudentID
GROUP BY Program

Now I will get results like:

Program Males Females Arts MalesAverage
---------------------------------------
Program 7     5       1    50.000000

The problem with this is that there are only 4 males and 3 females, and that when I add the tblActivites, it seems to give duplicates. After looking through I noticed that there were 12 ChildID's in the Activities table, the reason being those Children were tied to more than one activity. I tried using something like:

SELECT SUM( DISTINCT CASE WHEN a.Gender LIKE 'M%' THEN 1 ELSE NULL END) AS Males

However it just returned 1 under the Males column. Any help would be appreciated. And just to clarify further, the tables are keyed together via the ChildID, I just need help making sure that I am returning the right information. If this is solved then my massive list of these things will be solved. Avoiding GROUP BY for everything is preferred seeing as I have a lot of data.

Would this work?

COUNT(DISTINCT CASE WHEN a.Gender LIKE 'M%' THEN a.ChildID ELSE NULL END) AS Males

I suspect there are better ways to do the join overall, but without knowing a lot more about the schema I really don't know.

You don't show the source data, but I'm willing to bet you have multiple rows per person per program.

You GROUP BY Program which aggregates per program, but if say John Smith has two rows with that program ID, he will get counted as MALE twice.

Please show us your table structure so we can give you a clearer query. Ideally you will get the count from a subselect using a GROUP BY on something like Person_ID so that you don't get duplicate counts.