Average of Sum minus Minimum

Question

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.

score 3 · Accepted Answer · edited May 23 '17 at 11:59

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.

score 1 · Answer 2 · answered Dec 02 '10 at 18:59

If you only need to drop one lowest grade (in case of ties)

SELECT  student_id, AVG(grade)
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY grade) rn
        FROM    my_tables
        )
WHERE   NOT (drop_hw = 1 AND rn = 1)
GROUP BY
        student_id

If you need to drop all lowest grades:

SELECT  student_id, AVG(grade)
FROM    (
        SELECT  *, MIN(grade) OVER (PARTITION BY student_id) mingrade
        FROM    my_tables
        )
WHERE   NOT (drop_hw = 1 AND grade = mingrade)
GROUP BY
        student_id

score 0 · Answer 3 · answered Dec 02 '10 at 19:00

0

The sum-operator gives one result (per group). The min-operator, too. So over what should the avg-operator aggregate?

answered Dec 02 '10 at 19:00

Dominik Seibold

2,439
1
23
29

This is a good point. I didn't think about that. How can I average the sum and minimum when there's no total number? – OneSneakyMofo Dec 02 '10 at 20:43
What do you want to do? I don't understand what you want to calculate. What is the sense of SUM-MIN? – Dominik Seibold Dec 02 '10 at 20:57
Okay, let's say these are the grades: (100, 100, 100, 90). If the class has a drop lowest grade, then using sum - min tt'd be 390 - 90 which gives me 300, then I find that average of that number, but I can't do that because it can't count the number of one thing and another thing like you said. – OneSneakyMofo Dec 03 '10 at 00:37
What about this: SELECT AVG(value) FROM (SELECT SUM(Grades.grade)-MIN(Grades.grade) AS value FROM ...) – Dominik Seibold Dec 03 '10 at 01:25

Average of Sum minus Minimum

3 Answers3