如何在mysql表中选择，平均和排序 [英] how to select, average and sort in mysql table

问题描述

我在mySql中有一张表格，如这张图片所示

i have a table in mySql like in this picture

我想编写一个查询，该查询的结果将按LESSON列进行分组，并添加新行，该行是LESSON列的平均值并求和CNT列的值....
对于此查询，我使用此

and i want to write a query which result will group by LESSON column, and add new row which is average value of LESSON column and sum CNT column values....
for this query i use this one

我使用此查询，但是它给出的结果如图3所示，在这种情况下，我无法按PERC排序

i use this query but it gives result like in picture 3 and i cant sort by PERC in this case

select no, STUD_ID,CLASS,LESSON, AVG(PERC) as PERC,SUM(CNT) as CNT from t_lesson where LESSON='CHEM' group by CLASS
union all
select no,STUD_ID,CLASS,'AVERAGE' as LESSON, AVG(PERC) as PERC, SUM(CNT) as CNT from t_lesson where LESSON='CHEM' group by LESSON

推荐答案

似乎您正在选择lesson为CHEM的所有行，然后想要一个额外的行，其平均行列式.怎么样:

It looks like you're selecting all of the rows where lesson is CHEM, and then you want an extra row with the average of the percantage. How about:

select *
from (
  -- this part gets all the "CHEM" rows
  select * 
  from <your_table_name>
  where lesson = "CHEM"
  union
  -- this parts selects the aggregate row
  select 
    NULL            as `no`, 
    NULL            as `stud_id`, 
    NULL            as `class`,
    "average"       as `lesson`,
    avg(percentage) as `perc`, 
    sum(count)      as `cnt`
  from <your_table_name>
  where lesson = "CHEM"
) q
order by `perc` desc;

请注意，排序是由外部查询执行的.

Note that the sorting is performed by the outer query.

这篇关于如何在mysql表中选择，平均和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！