一行(两列的avg值)和同一行(另一列的值)之和

Question

​

​

我写了一个查询，但它显示了错误。我想得到mid_1_english和mid_2_english的AVG，然后用semester_final_english添加这个AVG。

select s_roll_no, s_f_name, s_l_name, s_class, 
           SUM(mid_1_english + mid_2_english)/2 as mid_english, 
           SUM(mid_1_mathematics + mid_2_mathematics)/2 as mid_mathematics, 
           SUM(mid_english + semester_final_english) as total_english, 
           SUM(mid_mathematics + semester_final_mathematics) as total_mathematics 
from elaborate_result 
group by s_f_name, s_l_name;
LIMIT 0, 50000 

错误代码: 1054。“字段列表”中的未知列“mid_english”0.000秒

Answer 1

当你做这样的事

    Select SUM(mid_1_english + mid_2_english)/2 as mid_english 

它不会创建一个新的列mid_english。因此，不能将其用作同一查询中的列。

所以你需要的可能是这样的

    select s_roll_no, s_f_name, s_l_name, s_class, 
       SUM(mid_1_english + mid_2_english)/2 as mid_english, 
       SUM(mid_1_mathematics + mid_2_mathematics)/2 as mid_mathematics, 
       SUM((mid_1_english + mid_2_english)/2 + semester_final_english) as 
     total_english, 
      SUM((mid_1_mathematics + mid_2_mathematics)/2 + 
     semester_final_mathematics) as total_mathematics 
    from elaborate_result 
  group by s_f_name, s_l_name;
 LIMIT 0, 50000