mysql两个聚合函数的总和不起作用

sqyvllje  于 2021-06-20  发布在  Mysql
关注(0)|答案(3)|浏览(405)

嗨,我想添加两个聚合函数的结果,但是我得到了“组函数的无效使用”。有人能纠正以下问题吗:

SELECT   mc.complaint_type_id,
         mc.complaint_type,
         sum(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
bybem2ql

bybem2ql1#

删除嵌套 sums 并添加 mc.complaint_typeGROUP 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

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

mrzz3bfm

mrzz3bfm2#

试试这个:

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 或者干脆把它拿走。

lsmepo6l

lsmepo6l3#

您需要在group by中指定mc.u 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

相关问题