SQL Calculations, Percentages, & Averages

[RaceStats]

I'm new to SQL and need some advice on performing various calculations. If someone could point me down the right path to get information on how to do these things I would appreciate it.

I'm trying to create a Racing Statistical Program. You can see thread "Selecting data with criteria from 3 or 4 tables thread 181-1301486" to see the beginning of creating a basic record set for the project.

I'm able to pull the records from an Access DB that I need but I don't know how to manipulate them. I've created a recordset that has basic calculations in it (SUM, AVG, COUNT, Etc.). The next step is that I need to do some more complicated calculations.

Here would be a recordset.

DriverName
TotalPoints - SUM(Results.ResultsPoints)
TotalStarts - COUNT(Results.ResultsPoints)
AvgPoints - AVG(Results.ResultsPoints)
High - MIN(Results.ResultsFinish)
AvgStart - AVG(Results.ResultsStart)
AvgFinish - AVG(Results.ResultsFinish)
TotalLaps - SUM(Events.EventLaps)
LapsComplete - SUM(Results.ResultsLapsComplete)
%LapsComplete - Calculate using prev. columns
AvgLast3
AvgLast4
AvgLast6
AvgLast8
HOT6
HOT8

One of the things that I need to calculate are averages at intervals. At a minimum I need to average out the points earned (per driver) over the last 3 races, 4 races, 6, & 8 races.

From there I then need to use those averages and perform some calculations on them. (They are so easy to do in Excel). Here are the basic calculations that I need to perform.

Delta-1 = Avg Past 6 - Season Average
Delta-2 = Avg Past 3 - Avg Past 6
(.75*Delta-2) + (.25*Delta-1)This would be the HOT6 & HOT8 in the result set above.

Next, Top 5's & Top 10's. This would be how many times the driver finished in the Top 5 and in the Top 10. I tried to do something like Count(Points < 6) which didn't work. So how do you count the number of times a field meets a criteria?

Percentages - How do you calculate percentages on fields in the result set? For example, you have columns that are the results of Total Starts (TS) and Number of Top 5's (T5). How do I perform the calculations on those 2 columns.

I'ld like to tackle this mess myself but I need pointed in the right direction. I don't know if all of this can be done with SQL or a combination of SQL and VB code. What Methods/Functions/Aggregates can I use? Subqueries? How many, broken down where? I have access to quite a few books but I need to know what to go looking for.

 

[lameid]

Do you have a way of determining how many races ago it was?

AVG(IIF((RaceNumberAgo) <=3,Results.ResultsPoints,Null))

The above will deterimine the average result points over the most three recent races where RaceNumberAgo is the number of races ago it is. Note that aggreagate functions ignore nulls.

If you don't have a way of getting at RaceNumberAgo, you can do the following. My instructions may not be specific enough for you so just ask if not... Add a counter field to your query with a value of 1. Then in the report you could set the running sum of a textbox to determine the race ago value (you will have to sort the races most recent to least recent). Then you can use that in a similar expression to calculate the value of IIF((RaceNumberAgo) <=3,Results.ResultsPoints,Null). Unfortunately it gets tricky here as you need two fields like that now, one as above with a running sum and one that returns 1 as the true part instead of points...
IIF((RaceNumberAgo) <=3,1,Null)
Finally you will have to take the value divided by the second counter control to get your average in a the section footer for whatever grouping is appropriate.

 

[RaceStats]

lameid,

first off thanks for helping out a rookie I truly appreciate it. To answer your question, there might be 2 ways of determining (RaceNumberAgo). This could be done by either EventDate or EventID. Let me give you the important table configuration to better explain.

Table - Events
EventID
EventDate
TrackID - Foreign Key to table Tracks

Table - Results
ResultID
ResultDate - Duplicate to EventDate above
EventID - Foreign Key to table Events
ResultPoints

The Results table is my primary source of data while the Events table contains selection criteria. Below is a query:

strSQL = "SELECT D.DriverFullName, SUM(R.ResultPoints), " & _
   "Count(R.ResultPoints), MIN(R.ResultFinish), " & _
   "AVG(R.ResultPoints), AVG(R.ResultStart), " & _
   "AVG(R.ResultFinish), SUM(R.ResultLapsComp), " & _
   "SUM(R.ResultWinnings), SUM(R.ResultAdjustment) " & _
   "FROM ((Results R INNER JOIN Drivers D ON R.DriverID = D.DriverID) " & _
   "INNER JOIN Events E ON R.EventID = E.EventID) " & _
   "INNER JOIN Tracks T ON E.TrackID = T.TrackID " & _
   "WHERE Year(R.ResultDate) > " & CStr(iYear) & " AND " & _
   "T.TrackTypeID = (SELECT TrackTypeID FROM Tracks WHERE TrackID=" & CStr(iTrackID) & ") " & _
   "GROUP BY D.DriverFullName"

In the above query all of the results are returned per Driver for all of the Events whose Track is the same TrackType as the Selected Track (combobox). The Tables/Fiedls are:

Results - DriverID, EventID, Points
Drivers - DriverID, DriverFullName
Events - EventID, TrackID
Tracks - TrackID, TrackTypeID
TrackType - TrackTypeID, TrackID

I would think that the Events.EventDate would be the best field to use as (RaceNumberAgo) because Events.EventID may not be sequential. So we would need to add Events.EventDate to the table schema above.

I hope this explains what I'm trying to do a little better. Any suggestions would be greatly appreciated.

 

[lameid]

Without jumping through a lot of extra hoops, I think the report suggestion in my original post is the best. You just need to sort descending by event date and kill all the aggregate parts of your query and do it in the report instead.

An alternative would be to either programmatically create a table that kept the most recent race numbers in it or maintain on manually. Both of these seem more trouble than they are worth to me in comparison to making the report. If you need the same logic repeatedly, then this might be the way to go.