SQL Inserting values to an existing record if they are meeting some criteria, or updating a value to maintain an average

Question

I am creating a database table to track stats on people running 100m sprints.
Each row (record) will hold the data for a runner.
A row will store how many times they have ran a 100m sprint. (totalRuns)
A row will also hold the average time the runner has based on the total number of runs they have done. (timeAverage)
A row will also hold the best time the runner has ever ran. (timebest)

Is there a way that I can directly insert a new _run's_ data into the table and have the average timeAverage field's value calculated instead of having to pull the runner's existing data out of the database first, calculating the average in my custom code and then inserting the calculated result?

When I insert a runner's first run, it is simple. Just input the data for that run like:

INSERT INTO runstats ('John Smith', 1, 11.43, 11.43)

so John Smith has run once, and he ran a 11.43 sprint so that's his current average and best time.

When John runs again, I just want to do something like (I know this doesnt work, this is just pseudo code):

INSERT OR UPDATE INTO runStats ('John Smith', AUTOINCREMENT+1, (IF thisRun < timeBest, thisRun, timeBest), (AVERAGEOF(timeAverage + thisRun, totalRuns))

So what I'm asking is can SQL (sqlite specifically) do inserts like this, where you can test against existing values in the table and update/increment them ?

Answer 1

I am specifically trying to avoid holding data that I don't have to, hence why i want it to update all this in the single SQL statement.

And I seem to have found the way to do it: use the UPDATE statement.

UPDATE <tableName> SET <fieldName> = [expression] WHERE [conditions]

Example:

UPDATE runStats SET runsTotal = runsTotal + 1, timeAverage = (timeAverage * runsTotal + 10.20) / (runsTotal + 1), timeBest = MIN(timeBest,10.20) WHERE runnerName = 'John Smith';

This achieves what I wanted. It will:
- increment runsTotal by 1
- add the current run time to the existing average and redetermine the overall average
- check if the current run time was lower than the existing best time and update the best time accordingly. (use MIN() for this)

I'm not 100% sure if the average time thing is accurate, i will have to look at that to ensure it is but i'm sure there will be some mathematical way to do what i want and now i can have the SQL engine decide what gets stored when the UPDATE calls occur.

Answer 2

Good for you 👍

Answer 3

I would suggest you to separate the runner info and the sprints info into different table. This way you can easily save sprints time record, query how many times a certain runner runs, and calculate average despite how many times each runner sprints.

[runner]
Id
Name

[sprints]
sprinter_Id # reference to [runner]
time

This way you can count how many sprints a certain runner did (totalRuns), average times (tineAverage) and of course the best time (timeBest).

You can optionally create another table (say, [stat]) which stores the run count, best time and average specifically, and have a trigger in [sprints] table manage the updates of the information in the table [stat]. And you can therefore just use the info from [stat] table.

Just a thought ; )