MySQL conditional SUM using GROUP BY and DISTINCT

Question

I have the following data structure in my foos table:

-----------------------------------------------
| 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 |
-----------------------------------------------

My goal is to get a success/total count for each bar_id for distinct baz_ids. For example:

------------------------------
| bar_id | successes | total |
------------------------------
| 1      | 1         | 2     |
| 2      | 1         | 2     |
| 3      | 2         | 3     |
------------------------------

Here is a query that works:

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

Is there a way to get the successes column using MySQL functions without doing a sub-select? Seems like there must be a way to use GROUP_CONCAT or one of the other Group By Functions to do this.

Edit

Using SUM(value="success") is close, but counts all the successes for a distinct baz_id instead of only counting a single success:

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     | <- Successes should be 1
| 2      | 1         | 2     |
| 3      | 3         | 3     | <- Successes should be 2
------------------------------

I would greatly appreciate feedback on how I can make this question better since it is getting down-voted. — , Dec 24 '13 at 19:41

score 21 · Accepted Answer · answered Dec 24 '13 at 19:33

21

You could use a CASE with DISTINCT to get the same result;

SELECT bar_id, 
  COUNT(DISTINCT CASE WHEN value='success' THEN baz_id ELSE NULL END) successes, 
  COUNT(DISTINCT baz_id) total
FROM foos
GROUP BY bar_id;

An SQLfiddle to test with.

answered Dec 24 '13 at 19:33

Joachim Isaksson

176,943
25
281
294

Excellent. This is exactly the type of thing I was looking for. And, as I had hoped, this shaves a significant amount of time (~40%) off of the query compared to the version I was using above. – Dec 24 '13 at 19:40

MySQL conditional SUM using GROUP BY and DISTINCT

1 Answers1