MySQL中统计3个含Yes/No值的列的Yes/No数量并合并展示

不用纠结DISTINCT COUNT，直接用条件聚合+UNION ALL就能实现需求，把各列的Yes/No统计结果合并成一张表。

示例SQL代码 #

假设你的数据表名为language_responses，执行以下查询即可：

SELECT
    'Yes' AS Key,
    COUNT(CASE WHEN French = 'Yes' THEN 1 END) AS French,
    COUNT(CASE WHEN Spanish = 'Yes' THEN 1 END) AS Spanish,
    COUNT(CASE WHEN English = 'Yes' THEN 1 END) AS English
UNION ALL
SELECT
    'No' AS Key,
    COUNT(CASE WHEN French = 'No' THEN 1 END) AS French,
    COUNT(CASE WHEN Spanish = 'No' THEN 1 END) AS Spanish,
    COUNT(CASE WHEN English = 'No' THEN 1 END) AS English
FROM language_responses;

输出结果 #

执行后会生成你需要的格式：

逻辑说明 #

1. 用CASE语句筛选出指定列的目标值（Yes/No），再通过COUNT统计符合条件的行数
2. 用UNION ALL将Yes行和No行的统计结果合并，最终得到两行多列的统计表格

内容的提问来源于stack exchange，提问作者Obeng Dapaah