title: MySQL conditional SUM using GROUP BY and DISTINCT
body: <p>I have the following data structure in my foos table:</p>
  
  <pre><code>-----------------------------------------------
  | id | bar_id | baz_id | date       | value   |
  -----------------------------------------------
  | 1  | 1      | 1      | 2013-12-01 | failure |
  | 2  | 1      | 1      | 2013-12-09 | failure |
  | 3  | 2      | 1      | 2013-12-02 | success |
  | 4  | 3      | 1      | 2013-12-10 | success |
  | 5  | 3      | 1      | 2013-12-01 | failure |
  | 6  | 3      | 1      | 2013-12-08 | success |
  | 7  | 1      | 2      | 2013-12-02 | success |
  | 8  | 1      | 2      | 2013-12-08 | failure |
  | 9  | 1      | 2      | 2013-12-03 | success |
  | 10 | 2      | 2      | 2013-12-07 | failure |
  | 11 | 2      | 2      | 2013-12-08 | failure |
  | 12 | 3      | 2      | 2013-12-04 | success |
  | 13 | 3      | 3      | 2013-12-14 | failure |
  -----------------------------------------------
  </code></pre>
  
  <p>My goal is to get a success/total count for each bar_id for distinct baz_ids. For example:</p>
  
  <pre><code>------------------------------
  | bar_id | successes | total |
  ------------------------------
  | 1      | 1         | 2     |
  | 2      | 1         | 2     |
  | 3      | 2         | 3     |
  ------------------------------
  </code></pre>
  
  <p>Here is a query that works:</p>
  
  <pre><code>SELECT foos.bar_id,
      successes,
      COUNT(distinct baz_id) as total
    FROM foos
      LEFT JOIN 
          (SELECT bar_id, count(distinct baz_id) as successes
            FROM foos
            WHERE value = "success"
            GROUP BY bar_id) as other
        ON foos.bar_id = other.bar_id
    GROUP BY bar_id
  </code></pre>
  
  <p><strong>Is there a way to get the successes column using MySQL functions without doing a sub-select?</strong> Seems like there must be a way to use <code>GROUP_CONCAT</code> or one of the other <a href="http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html" rel="nofollow">Group By Functions</a> to do this.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>Using <code>SUM(value="success")</code> is close, but counts all the successes for a distinct baz_id instead of only counting a single success:</p>
  
  <pre><code>SELECT bar_id,
      SUM(value="success") AS successes,
      COUNT(distinct baz_id) as total
    FROM foos   
    GROUP BY bar_id
  
  ------------------------------
  | bar_id | successes | total |
  ------------------------------
  | 1      | 2         | 2     | &lt;- Successes should be 1
  | 2      | 1         | 2     |
  | 3      | 3         | 3     | &lt;- Successes should be 2
  ------------------------------
  </code></pre>