SQL关联AB表统计value1、value2非空计数之和实现total_count问题

你只需要把sum(2) AS total_count替换为两个计数结果直接相加即可，count()统计非空值的逻辑已经符合你的需求，直接求和两个统计值就能得到正确的总计数。
另外你当前语句里的DISTINCT是冗余的，GROUP BY已经保证了每个entity_id只会返回一行，不需要额外去重。

修正后的完整查询语句 #

SELECT a.entity_id, a.name,
       COUNT(b.value1) AS value1_count,
       COUNT(b.value2) AS value2_count,
       COUNT(b.value1) + COUNT(b.value2) AS total_count
FROM a
LEFT JOIN b ON a.entity_id = b.entity_id
GROUP BY a.entity_id, a.name

原语句错误原因 #

你用sum(2)相当于对左连接后每个匹配到的表B行都累加2，比如entity_id=2在表B有2条匹配行，sum(2)会得到4，和你预期的3不符，而直接对两个非空计数求和才是正确的总非空值统计逻辑。

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