SQL统计两表分类/状态计数及总计结果错误（应为22实际返回242）

现有两张数据表，需完成三项统计：

• 不同分类值（F、M、O）的计数
• 不同状态值（GL、PD）的计数
• 上述所有统计项的总计值（正确总计应为22）
  当前查询返回的总计值始终为242，不符合预期：
  

当前使用的SQL代码：

select crimes.classification, count(crimes.classification) as 'classifications/statuses'
from crimes
group by classification
union all
select crime_charges.charge_status, count(crime_charges.charge_status)
from crime_charges
group by charge_status
union all
select 'Total', count(crime_charges.charge_status) + count(crimes.classification)
from crime_charges, crimes;

最后一段总计逻辑使用from crime_charges, crimes写法会触发隐式交叉连接，生成两张表的笛卡尔积：两表的每一行都会互相匹配，最终统计的基数是两张表行数的乘积，因此计数结果远大于真实值。

总计部分改为分别统计两张表的对应行数后再求和，避免笛卡尔积问题：

select crimes.classification, count(crimes.classification) as 'classifications/statuses'
from crimes
group by classification
union all
select crime_charges.charge_status, count(crime_charges.charge_status)
from crime_charges
group by charge_status
union all
select 'Total', 
  (select count(charge_status) from crime_charges) + (select count(classification) from crimes)
;

如果对应字段存在NULL值且需要将NULL纳入统计，将count(字段名)替换为count(*)即可。

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