Reputation: 27
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
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
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
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