Average of Sum minus Minimum
Hey guys, I have a 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.
Something like this:
AVG(Grades.grade) OVER (PARTITION BY Grades.student_id) AS avg_of_grades
(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.