DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to Create a Rolling Average

  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Cool How to Create a Rolling Average

    Hello There,
    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!


  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2004
    Posts
    2

    Cool

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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253


    your god, not mine.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width