MySQL 条件 SUM 使用 GROUP BY 和 DISTINCT

Question

我的 foos 表中有以下数据结构：

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

我的目标是为不同baz_ids的每个bar_id获得成功/总数。例如：

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

下面是一个有效的查询：

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

有没有办法使用 MySQL 函数获取成功列而无需进行子选择？似乎必须有一种方法可以使用GROUP_CONCAT或其他按功能分组之一来执行此操作。

编辑

使用 SUM(value="success") 很接近，但会计算不同baz_id的所有成功，而不是只计算单个成功：

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

Answer 1

您可以使用带有DISTINCT的CASE来获得相同的结果;

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;

用于测试的 SQL小提琴。