MySQL GROUP BY CASE 错误

我正在尝试使用 GROUP BY CASE 语句来量化我的查询输出。 我在 SELECT 部分、GROUP BY 部分以及两者中都尝试了 CASE，但仍然得到“错误代码：1111。组功能使用无效。”

这是我的代码（两个部分都有 CASE）

SELECT 
case when BBsum.sum <= 9999 then '<10K'
    when BBsum.sum <= 10999 then '<11K'
    when BBsum.sum <= 11999 then '<12K'
    when BBsum.sum <= 12999 then '<13K'
    when BBsum.sum <= 13999 then '<14K'
    when BBsum.sum <= 14999 then '<15K'
    when BBsum.sum <= 15999 then '<16K'
    when BBsum.sum <= 16999 then '<17K'
    when BBsum.sum <= 17999 then '<18K'
    when BBsum.sum <= 18999 then '<19K'
    when BBsum.sum <= 19999 then '<20K'
    else '20K+' end as sum, 
avg(sum(CASE when C.finance_type = 'INSTALLMENT_LOAN' then C.paid_amount end)/sum(CASE when C.finance_type = 'INSTALLMENT_LOAN' then C.initial_amount end)) as IL_RR,
avg(sum(CASE when C.finance_type = 'PAYDAY_LOAN' then C.paid_amount end)/sum(CASE when C.finance_type = 'PAYDAY_LOAN' then C.initial_amount end)) as PDL_RR,
avg(sum(C.paid_amount)/sum(C.initial_amount)) as RR
FROM 
credit C
left join (select BB.borrower_id id, sum(BB.points) sum from borrower_bonus BB
where BB.type = 'CHARGE'
group by bb.borrower_id) BBsum on C.borrower_id = BBsum.id
where C.status IN ('EXPIRED', 'COMPLETED','SOLD')
group by case when BBsum.sum <= 9999 then '<10K'
    when BBsum.sum <= 10999 then '<11K'
    when BBsum.sum <= 11999 then '<12K'
    when BBsum.sum <= 12999 then '<13K'
    when BBsum.sum <= 13999 then '<14K'
    when BBsum.sum <= 14999 then '<15K'
    when BBsum.sum <= 15999 then '<16K'
    when BBsum.sum <= 16999 then '<17K'
    when BBsum.sum <= 17999 then '<18K'
    when BBsum.sum <= 18999 then '<19K'
    when BBsum.sum <= 19999 then '<20K'
    else '20K+' end
order by sum DESC

我怀疑我必须做一些更简单的事情才能实现我想要的（将 RR 平均值除以收到的奖金组）

您正在嵌套聚合函数，MySQL 不允许这样做。

这可能就是您想要的：

SELECT 
case when BBsum.sum <= 9999 then '<10K'
    when BBsum.sum <= 10999 then '<11K'
    when BBsum.sum <= 11999 then '<12K'
    when BBsum.sum <= 12999 then '<13K'
    when BBsum.sum <= 13999 then '<14K'
    when BBsum.sum <= 14999 then '<15K'
    when BBsum.sum <= 15999 then '<16K'
    when BBsum.sum <= 16999 then '<17K'
    when BBsum.sum <= 17999 then '<18K'
    when BBsum.sum <= 18999 then '<19K'
    when BBsum.sum <= 19999 then '<20K'
    else '20K+' end as sum, 
sum(CASE when C.finance_type = 'INSTALLMENT_LOAN' then C.paid_amount end)
   /sum(CASE when C.finance_type = 'INSTALLMENT_LOAN' then C.initial_amount end) as IL_RR,
sum(CASE when C.finance_type = 'PAYDAY_LOAN' then C.paid_amount end)
   /sum(CASE when C.finance_type = 'PAYDAY_LOAN' then C.initial_amount end) as PDL_RR,
sum(C.paid_amount)/sum(C.initial_amount) as RR
FROM 
credit C
left join (
    select BB.borrower_id id, sum(BB.points) sum 
    from borrower_bonus BB
    where BB.type = 'CHARGE'
    group by bb.borrower_id
) BBsum on C.borrower_id = BBsum.id
where C.status IN ('EXPIRED', 'COMPLETED','SOLD')
group by case when BBsum.sum <= 9999 then '<10K'
    when BBsum.sum <= 10999 then '<11K'
    when BBsum.sum <= 11999 then '<12K'
    when BBsum.sum <= 12999 then '<13K'
    when BBsum.sum <= 13999 then '<14K'
    when BBsum.sum <= 14999 then '<15K'
    when BBsum.sum <= 15999 then '<16K'
    when BBsum.sum <= 16999 then '<17K'
    when BBsum.sum <= 17999 then '<18K'
    when BBsum.sum <= 18999 then '<19K'
    when BBsum.sum <= 19999 then '<20K'
    else '20K+' end
order by sum DESC