Count number of students above and below the average score in SQL

user_8291606 0
Viewed 1102

I have a sample table below and I am trying to get the number of student above the average score and number of students below the average score.

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

The target result table is supposed to look like

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

I have been able to write a query to get the names of students above the average score and this can be easily edited to get the count of students below the avg score.

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

The issue here is that I want to add the counts in a table indicating the number of students above and below the average score.

If a number is equal to the average score, it can be considered as above the average score.

window-functions count group-by mysql sql
1 Answers

If you are running MySQL 8.0, you can use window functions and aggregation:

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

In earlier versions, you can join the table with an aggregate query that computes the average score per subject:

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

Edit: you appear to be running Big Query, not MySQL as initially tagged. You can use 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
user_106767160
Related