Average of Sum minus Minimum

I have an SQL statement that grabs the grades of different activity types (Homework, Quiz, etc), and if there’s a drop lowest for that type, it drops, else, it remains. The errors are below as well as the SQL Code.

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 ...

Here are the errors I’m getting:

- 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.  

Answer

Look into analytical functions. (SO question, Oracle documentation).

Something like this:

AVG(Grades.grade) OVER (PARTITION BY Grades.student_id) AS avg_of_grades

and:

(AVG(SUM(Grades.grade) - MIN(Grades.grade))) OVER (PARTITION BY Grades.student_id) AS avg_grades_with_drop

Set the partitioning with whatever makes sense in your case; we can’t tell since you omitted the FROM ... in your example.

You can then use those column aliases in any calculations inside your CASE statement.