两个聚合函数的mysql和不工作

移除嵌套 sums 并添加 mc.complaint_type 到 GROUP BY 条款。如果需要添加两个聚合函数的值,请使用 + 运算符,而不是聚合函数。

SELECT   
  mc.complaint_type_id,
  mc.complaint_type,
  sum(case when c.is_solved = 1 then 1 else 0 end) + sum(case when c.is_solved = 0 and c.res_user_id is null then 1 else 0 end) as complaints_count,
FROM svk_apt_master_complaints mc
LEFT JOIN svk_apt_complaints c ON 
  c.complaint_type_id = mc.complaint_type_id 
  and c.is_active = 1
  and c.customer_id = 1 
  and c.association_id = 1
GROUP BY mc.complaint_type_id, mc.complaint_type

我还重新格式化了您的代码并删除了不必要的括号。

您需要在group by中指定mc.complaint_type列

SELECT   mc.complaint_type_id,
         mc.complaint_type,
         sum(case when c.is_solved = 1 then 1 else 0 end) + sum(case when c.is_solved = 0 and c.res_user_id is null then 1 else 0 end) as complaints_count,
from  svk_apt_master_complaints mc
left join svk_apt_complaints c on c.complaint_type_id = mc.complaint_type_id and c.is_active = 1 and c.customer_id = 1 and c.association_id = 1
group by mc.complaint_type_id,mc.complaint_type

试试这个:

SELECT   mc.complaint_type_id,
         mc.complaint_type,
         sum(case when c.is_solved = 1 then 1 else 0 end) + sum(case when ((c.is_solved = 0) and (c.res_user_id is null)) then 1 else 0 end) as complaints_count
from  svk_apt_master_complaints mc
left join svk_apt_complaints c on c.complaint_type_id = mc.complaint_type_id 
where c.is_active = 1 and c.customer_id = 1 and c.association_id = 1
group by mc.complaint_type_id, mc.complaint_type

你不需要 sum() 当你使用 + 操作员。也, SUM 是聚合函数。

此外,还可以选择不包含在聚合中的列: mc.complaint_type . 你需要是否包括在 group by 或者把它取下来。