CODEWITHSUNDEEP
mysql
Charly Mtz P
Charly Mtz P

Reputation: 27

How can I obtain the average of students with non-failing subjects?

I have this data in the table:

id_student | Grade 
   1       |  8 
   2       | 10
   3       |  6-- Fail
   1       | 10

Each grade references to a different subject and the expected result should be:

id_student | AVG 
   1       |  9 
   2       | 10

Help please!

Upvotes: 0

Views: 56

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133410

You could use a not in and a subselect (and obvious a group by for AVG)

select id_student, avg(grade)
from my_table
where id_student not in (select id_student 
            from my_table where grade <= 6)
group by id_student

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

Similar to @scaisEdge, but instead of a subquery you use a conditional count to see how many fails grade have each student. Then only select the one with 0 fails.

select id_student, avg(grade)
from my_table
group by id_student
HAVING COUNT(CASE WHEN grade <= 6 THEN 1 END) = 0

Upvotes: 1

Quartal
Quartal

Reputation: 410

SELECT AVG(Grade) FROM TABLE WHERE Grade > 6

So this selects the avg of grades from your table (change the TABLE to your table name) where the grade is above the failing grade (I assume its 6 from your question)

Upvotes: 0

Related Questions