How to Create a Rolling Average
I am sure this question isn't as hard as I am making it but this newbie still needs some assistance.
How would I go about create a rolling average? For example, I have 7 exam grades, each exam is taken on a different day. For the last 5 tests find the average. If the average is below the average of ALL previous groups of 5 Grades return the set of: student_name, class_name , grade , exam_date , teacher_name for the last 5 exams.
If I have seven grades, then my first group of 5 grades would be grades 7 to 3, the second group would be grades 6 to 2, and the third group would be grades 5 to 1 and in this case there would not be a fourth group of five. I have to determine the number of groups and their averages then compare them to the lastest average and then report out the required info.
Here are the relationships that I am using:
Student (student_id, student_name)
Teacher (teacher_id, teacher_name)
Exam (teacher_id, exam_id, class_id, exam_name, exam_date)
Grade(exam_id, student_id, grade)
Class ( class_id,class_name)
Bonus (student_id, class_id, bonus)
Thanks for just pointing me in the right direction!
Have you looked at using Analytic Functions? They do exactly this kind of thing, where you can define a "working range" for an aggregate function. for example, give me the average of the five rows prior to this one when the order is defined by the following columns" etc.
Have a look at the SQL Reference at http://tahiti.oracle.com -- there are examples there that will be a close fit to what you are trying to do.
Thanks for the assistance! I really appreciate it! I was unaware of these functions. I searched the documentation; however the terminology I was using was incorrect.
Here is some informtion in refernece to how you closed your previous reply:
"Thou shalt not take the name of the Lord thy God in vain: for the Lord will not hold him guiltless that taketh His name in vain" (Exodus 20:7).
your god, not mine.
Click Here to Expand Forum to Full Width