SQL分组计数：如何简便实现包含0值的按姓名年份统计？

原始表df的数据如下：

name year var1
  John 2010    a
  John 2010    b
  John 2011    a
  John 2011    b
  John 2013    b
  John 2013    b
  Jane 2010    a
  Jane 2010    a
  Jane 2011    b
  Jane 2011    b

需求：统计每个name每年中var1='a'的出现次数，且需要包含所有存在记录的年份（即使该年份没有'a'，也要显示次数为0）。

现有方法的问题 #

• 第一种经典方法通过WHERE var1='a'过滤后分组，会直接丢失没有'a'的年份（比如John的2013年），无法满足需求。
• 第二种自连接方法虽然能得到正确结果，但需要额外的CTE生成唯一的name+year组合，写法繁琐冗余。

更简便的标准实现 #

不需要自连接或CTE，直接对name和year分组，结合CASE语句求和即可：

SELECT name, year, SUM(CASE WHEN var1 = 'a' THEN 1 ELSE 0 END) AS sum_var1
FROM df
GROUP BY name, year;

原理说明

去掉WHERE var1='a'的过滤后，所有存在的name+year组合都会被保留。CASE语句将var1='a'的记录计为1，其余计为0，SUM求和后自然得到对应年份'a'的出现次数；没有'a'的年份，SUM结果就是0，完全符合需求。

执行后的输出与第二种方法一致：

name year sum_var1
1 Jane 2010        2
2 Jane 2011        0
3 John 2010        1
4 John 2011        1
5 John 2013        0

进一步简化写法（根据SQL方言） #

在支持布尔值转整数的SQL方言（如PostgreSQL、MySQL）中，可以用更简洁的写法：

SELECT name, year, SUM(CAST(var1 = 'a' AS INT)) AS sum_var1
FROM df
GROUP BY name, year;

在SQL Server、Access等支持IIF函数的方言中，可写成：

SELECT name, year, SUM(IIF(var1='a', 1, 0)) AS sum_var1
FROM df
GROUP BY name, year;

这些写法更简洁直观，是统计这类条件出现次数的标准方案。

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