oracle sum后减,sql – Sum的平均值减去最小值

嘿伙计们,我有一个SQL语句,可以获取不同活动类型(家庭作业,测验等)的等级,如果该类型的分数最低,则会下降,否则,它仍然存在.错误如下以及SQL代码.

SELECT Student.firstName, Student.lastName, 'Grades' =

CASE

WHEN Grades.activityType = 'Homework' THEN

CASE WHEN Policy.drop_hw = 1 THEN

(AVG(SUM(Grades.grade) - MIN(Grades.grade))) * (Policy.homework / 100)

ELSE

(AVG(Grades.grade) * (Policy.homework / 100))

END

END, Course.courseNum, Course.sectNum, Grades.activityType

FROM ...

以下是我得到的错误：

- Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

- Column 'Policy.drop_hw' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.