MySQL多count()与sum()组合查询：按行生成总计列的问题

我需要编写一条MySQL查询语句，从Records表中提取User列和caseType列（该列可选值为FAXCOM、IMA、DREAM），统计每个用户各caseType且状态为CLOSED的次数，同时计算这三个统计列的行合计值，生成TOTAL列。

已尝试的步骤 #

1. 按用户分组统计各caseType数量 #

先写出了按User分组统计的查询：

select User,
       count(case when caseType = 'FAXCOM' and Status='CLOSED' then 0 end) as FAXCOM,
       count(case when caseType = 'IMA' and Status='CLOSED' then 0 end) as IMA,
       count(case when caseType = 'DREAM' and Status='CLOSED' then 0 end) as DREAM 
from Records group by User;

查询结果：

2. 单独计算行总计列 #

接着写出了单独计算总计列的查询：

Select User, SUM(FAXCOM+IMA+DREAM) as TOTAL from 
       (select User,
       count(case when caseType = 'FAXCOM' and Status='CLOSED' then 0 end) as FAXCOM,
       count(case when caseType = 'IMA' and Status='CLOSED' then 0 end) as IMA,
       count(case when caseType = 'DREAM' and Status='CLOSED' then 0 end) as DREAM
       from Records group by User ) 
as Total group by User;

查询结果：

3. 合并查询失败 #

尝试将两个查询合并时出现问题：总计列计算了所有行的总和（13），而非每行的合计值。尝试的合并语句如下：

select User,
       count(case when caseType = 'FAXCOM' and Status='CLOSED' then 0 end) as FAXCOM,
       count(case when caseType = 'IMA' and Status='CLOSED' then 0 end) as IMA,
       count(case when caseType = 'DREAM' and Status='CLOSED' then 0 end) as DREAM,
(Select SUM(FAXCOM+IMA+DREAM) as TOTAL from 
       (select User,
       count(case when caseType = 'FAXCOM' and Status='CLOSED' then 0 end) as FAXCOM,
       count(case when caseType = 'IMA' and Status='CLOSED' then 0 end) as IMA,
       count(case when caseType = 'DREAM' and Status='CLOSED' then 0 end) as DREAM
       from Records) as Total group by User)
from Records group by User;

错误结果：

解决方案 #

不需要嵌套多层子查询，直接在分组统计的基础上计算行合计即可，同时建议将count()替换为sum()，逻辑更清晰：

方案1：直接在主查询中计算合计 #

select 
    User,
    sum(case when caseType = 'FAXCOM' and Status='CLOSED' then 1 else 0 end) as FAXCOM,
    sum(case when caseType = 'IMA' and Status='CLOSED' then 1 else 0 end) as IMA,
    sum(case when caseType = 'DREAM' and Status='CLOSED' then 1 else 0 end) as DREAM,
    sum(case when caseType = 'FAXCOM' and Status='CLOSED' then 1 else 0 end) +
    sum(case when caseType = 'IMA' and Status='CLOSED' then 1 else 0 end) +
    sum(case when caseType = 'DREAM' and Status='CLOSED' then 1 else 0 end) as TOTAL
from Records 
group by User;

方案2：用子查询避免重复计算 #

select 
    User,
    FAXCOM,
    IMA,
    DREAM,
    FAXCOM + IMA + DREAM as TOTAL
from (
    select 
        User,
        sum(case when caseType = 'FAXCOM' and Status='CLOSED' then 1 else 0 end) as FAXCOM,
        sum(case when caseType = 'IMA' and Status='CLOSED' then 1 else 0 end) as IMA,
        sum(case when caseType = 'DREAM' and Status='CLOSED' then 1 else 0 end) as DREAM
    from Records 
    group by User
) as user_stats;

两种方案的正确结果：

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