mysql - 统计SQL中高于和低于平均分的学生数

Question

我在下面有一个示例表，我试图让高于平均分的学生人数和低于平均分的学生人数。

name    subject classroom   classarm    session     first_term_score    first_term_grade
std1    math    nursery 1A  nursery1    2018/2019     90                 A
std2    eng     nursery 1A  nursery1    2018/2019     70                 A
std3    sci     nursery 1A  nursery1    2018/2019     60                 B
std1    eng     nursery 1A  nursery1    2018/2019     64                 B
std2    math    nursery 1A  nursery1    2018/2019     70                 A

目标结果表应该看起来像

subject   avg_score count_above   count_below
math         80      1                1
eng          65.5    2                0

我已经能够编写一个查询来获取高于平均分的学生的姓名，并且可以轻松地对其进行编辑以获取低于平均分的学生人数。

SELECT name 
    FROM (SELECT name,
                 AVG(first_term_score) AS average_result
          FROM seveig
          GROUP BY name) sa, 
         (SELECT (AVG(first_term_score)) tavg
          FROM seveig) ta
     WHERE sa.average_result > ta.tavg

这里的问题是我想在表中添加计数，以指示高于和低于平均分的学生人数。

如果一个数字等于平均分，则可以认为它高于平均分。

Answer 1

如果您运行的是 MySQL 8.0，则可以使用窗口函数和聚合:

select
    subject,
    avg_score,
    sum(first_term_score >= avg_score) count_above,
    sum(first_term_score <  avg_score) count_below
from (
    select t.*, avg(first_term_score) over(partition by subject) avg_score
    from mytable t
) t
group by subject, avg_score

在早期版本中，您可以将表与计算每个主题的平均分数的聚合查询连接起来:

select
    t.subject,
    a.avg_score,
    sum(t.first_term_score >= a.avg_score) count_above,
    sum(t.first_term_score <  a.avg_score) count_below
from mytable t
inner join (
    select subject, avg(first_term_score) avg_score
    from mytable
    group by subject
) a on a.subject = t.subject
group by t.subject, a.avg_score

编辑 :您似乎在运行 Big Query，而不是最初标记的 MySQL。您可以使用 COUNTIF() :

select
    subject,
    avg_score,
    countif(first_term_score >= avg_score) count_above,
    countif(first_term_score <  avg_score) count_below
from (
    select t.*, avg(first_term_score) over(partition by subject) avg_score
    from mytable t
) t
group by subject, avg_score