title: SQL SUM and CASE and DISTINCT
body: <p>I've been trying to re-do this statement but to no avail.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Now I will get results like:</p>
  
  <pre><code>Program Males Females Arts MalesAverage
  ---------------------------------------
  Program 7     5       1    50.000000
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>SELECT SUM( DISTINCT CASE WHEN a.Gender LIKE 'M%' THEN 1 ELSE NULL END) AS Males
  </code></pre>
  
  <p>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.</p>