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.  

Answers


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.


Need Your Help

detect iPad 2x button for iPhone app

iphone ipad

Is there a way to detect that your iPhone app us running 2x/1x on an iPad?

Consuming JSON request and producing different output

java json rest restful-url

Can you point me to article or explain me how to declare RESTful web service which consumes JSON request and based on parameter inside JSON produces output in different formats, meaning customer ca...