需要显示各班级各科低于年级平均分的人数

Question

需要显示各班级各科低于年级平均分的人数
我的不对
SELECT
CLASSNO,
COURSE,
COUNT ( SELECT GRADE FROM STSCORE WHERE GRADE < ( SELECT AVG( GRADE ) FROM STSCORE GROUP BY COURSE, CLASSNO ) )
FROM
STSCORE

Answer 1

select classno,course,avgg,count(*) num
from (
select a.classno,a.course,a.grade,a.studentno,t1.avgg 
from STSCORE a JOIN
(select avg(grade) avgg,classno,course from stsco GROUP BY classno,course) t1 
ON a.classno = t1.classno and a.course=t1.course
) t2
GROUP BY classno,course
HAVING grade<avgg