mysql - 一般错误 : 1111 Invalid use of group function

Question

我有下面的 sql 查询，但是当我尝试运行查询时，它抛出了一个没有 1111 的错误，请帮忙。

select count( distinct id_student) count, 
CASE 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') < CURDATE()) THEN 'Subscription Expired' 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') >= CURDATE() && sum(completed_test_count) = sum(total_test_count) ) THEN 'Test Completed' 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') >= CURDATE() && sum(completed_test_count) < sum(total_test_count) ) THEN 'Active' 
END status 
from `order` 
inner join `student` on `id_student` = `order`.`student_id_student` 
inner join `order_has_test` on `order_id_order` = `order`.`id_order` 
inner join `subscription_plan` on `id_subscription_plan` = `subscription_plan_id_subscription_plan` 
where `subscription_plan_id_subscription_plan` = 1 
group by `order`.`student_id_student`, 
CASE 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') < CURDATE()) THEN 'Subscription Expired' 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') >= CURDATE() && sum(completed_test_count) = sum(total_test_count) ) THEN 'Test Completed' 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') >= CURDATE() && sum(completed_test_count) < sum(total_test_count) ) THEN 'Active' 
END 

Answer 1

分组依据应该只包含大小写

select count(distinct id_student) count, 
CASE 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') < CURDATE()) THEN 'Subscription Expired' 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') >= CURDATE() && sum(completed_test_count) = sum(total_test_count) ) THEN 'Test Completed' 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') >= CURDATE() && sum(completed_test_count) < sum(total_test_count) ) THEN 'Active' 
END status 
from `order` 
inner join `student` on `id_student` = `order`.`student_id_student` 
inner join `order_has_test` on `order_id_order` = `order`.`id_order` 
inner join `subscription_plan` on `id_subscription_plan` = `subscription_plan_id_subscription_plan` 
where `subscription_plan_id_subscription_plan` = 1 
group by
CASE 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') < CURDATE()) THEN 'Subscription Expired' 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') >= CURDATE() && sum(completed_test_count) = sum(total_test_count) ) THEN 'Test Completed' 
    WHEN (date_format(max(expiry_date),'%Y-%m-%d') >= CURDATE() && sum(completed_test_count) < sum(total_test_count) ) THEN 'Active' 
END