如何在MySQL的GROUP_CONCAT()中统计'pass'与'fail'的出现次数？

我需要统计以下语句中'pass'和'fail'的出现次数：

GROUP_CONCAT(parameters.calc_value0 ORDER BY samples.id ASC SEPARATOR '\n')

当前实现代码 #

SELECT parametertypes.name as 'Test',

CASE   
   WHEN (IFNULL(GROUP_CONCAT(parameters.calc_value0 ORDER BY samples.id ASC SEPARATOR '\n'), (GROUP_CONCAT(parameters.value0 ORDER BY samples.id ASC SEPARATOR '\n'))) LIKE '%pass%')
    THEN CONCAT('<span class="green">', if(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%pass%', COUNT(samples.id), COUNT(samples.id)) , ' pass </span>', CHAR(10), GROUP_CONCAT(DISTINCT CONCAT(samples.DDF_Artikelbezeichnung) ORDER BY samples.name ASC SEPARATOR '\n')) 
   
   ELSE '-'
END as 'Samples Pass',

CASE    
   
   WHEN (IFNULL(GROUP_CONCAT(parameters.calc_value0 ORDER BY samples.id ASC SEPARATOR '\n'), (GROUP_CONCAT(parameters.value0 ORDER BY samples.id ASC SEPARATOR '\n'))) LIKE '%fail%')
    THEN CONCAT('<span class="red">', if(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%fail%', COUNT(samples.id), COUNT(samples.id)), ' fail </span>', CHAR(10), GROUP_CONCAT(DISTINCT CONCAT(samples.DDF_Artikelbezeichnung) ORDER BY samples.name ASC SEPARATOR '\n'))
  
   ELSE '-'
END as 'Samples Fail',

CASE     
   WHEN (IFNULL(GROUP_CONCAT(parameters.calc_value0 ORDER BY samples.id ASC SEPARATOR '\n'), (GROUP_CONCAT(parameters.value0 ORDER BY samples.id ASC SEPARATOR '\n'))) LIKE '%documentation%')
    THEN CONCAT('<span class="orange">', if(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%documentation%', COUNT(samples.id), COUNT(samples.id)), ' documentation </span>',CHAR(10), samples.DDF_Artikelbezeichnung)      
  
   ELSE '-'
END as 'Samples Documentation'


FROM samples  
JOIN parameters ON parameters.sample_id = samples.id
JOIN parametertypes ON parametertypes.id = parameters.parametertype_id 

WHERE samples.order_id IN(1268)
AND samples.visible_to != 'deleted'
AND parameters.calc_value0 IS NOT NULL 

GROUP BY parametertypes.id -- , samples.DDF_Artikelbezeichnung

当前问题 #

当前结果不符合预期，第一行应显示9个pass和1个fail，但现有代码无法正确统计对应状态的数量。

解决方案 #

问题出在使用COUNT(samples.id)统计数量，它会返回分组内的总样本数，而非符合pass/fail条件的数量。需要改用SUM()结合条件判断来统计特定状态的出现次数：

修改后的SQL代码 #

SELECT parametertypes.name as 'Test',
    -- 统计Pass数量
    CASE
        WHEN SUM(IF(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%pass%', 1, 0)) > 0
        THEN CONCAT('<span class="green">', SUM(IF(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%pass%', 1, 0)), ' pass </span>', CHAR(10), GROUP_CONCAT(DISTINCT samples.DDF_Artikelbezeichnung ORDER BY samples.name ASC SEPARATOR '\n'))
        ELSE '-'
    END as 'Samples Pass',
    -- 统计Fail数量
    CASE
        WHEN SUM(IF(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%fail%', 1, 0)) > 0
        THEN CONCAT('<span class="red">', SUM(IF(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%fail%', 1, 0)), ' fail </span>', CHAR(10), GROUP_CONCAT(DISTINCT samples.DDF_Artikelbezeichnung ORDER BY samples.name ASC SEPARATOR '\n'))
        ELSE '-'
    END as 'Samples Fail',
    -- 统计Documentation数量
    CASE
        WHEN SUM(IF(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%documentation%', 1, 0)) > 0
        THEN CONCAT('<span class="orange">', SUM(IF(IFNULL(parameters.calc_value0, parameters.value0) LIKE '%documentation%', 1, 0)), ' documentation </span>', CHAR(10), GROUP_CONCAT(DISTINCT samples.DDF_Artikelbezeichnung ORDER BY samples.name ASC SEPARATOR '\n'))
        ELSE '-'
    END as 'Samples Documentation'
FROM samples  
JOIN parameters ON parameters.sample_id = samples.id
JOIN parametertypes ON parametertypes.id = parameters.parametertype_id 
WHERE samples.order_id IN(1268)
AND samples.visible_to != 'deleted'
AND parameters.calc_value0 IS NOT NULL 
GROUP BY parametertypes.id

核心修改点 #

1. 用SUM(IF(条件, 1, 0))替代COUNT(samples.id)，仅累加符合对应状态的记录数
2. 状态判断直接基于单条记录的calc_value0/value0，而非拼接后的字符串，更准确高效
3. 统一Samples Documentation的样本名称拼接逻辑，避免原代码中直接引用samples.DDF_Artikelbezeichnung导致的分组问题

内容的提问来源于stack exchange，提问作者Peter Sypek