Selecting pairs of records from database with SQL statements

Kyle,

After trying 100 different ways that wouldn't work I went back to my first instinct of using a self join and managed to get it to work.

Please Login or Register  to view this content.

I can run the rest of what I need in Excel without too much trouble but if I can get a SQL fix that would be great.

Basically my SELECT will give me a set of data for a specific b.DT (whatever DT I choose).
My dataset will have many a.DT all with the same b.DT.
For each a.DT I want to work out the average difference in scores (average of b.Sc-a.Sc for each a.DT)

In my one record example:
average b.AAA425 a.AAA500 = (80-78)/1 = 2

The purpose is to attempt to predict what AAA500 test score a student will get based on their AAA425 test score. Ideally some sort of regression would be the optimal method but average across a sufficient dataset should achieve the same result.

I am guessing my SQL statement might look something like:

Please Login or Register  to view this content.

Blue text = average of b.Sc-a.Sc
Red Text is another column that is b.Sc-a.Sc

Can I incorporate the red or blue text into my statement? If so HOW?


EDIT:

I am aware that if I can run the calculation of the average in my SQL statement then I only need to select AVG and order by a.DT. So the question is how to get the average for each a.DT in my dataset.
Something like this maybe?

Please Login or Register  to view this content.

Will each student only take each test once? Or could Adam J take mutltple AAA425 and AAA500s?

Edit actually just reread the sample data, the same test can be taken multiple times. So How do you decide what needs to be averaged here:

Please Login or Register  to view this content.