SQL SUM and CASE and DISTINCT

Question

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.

Answer 1

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.