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.