Ask the Expert

Performing INSERT calculations

I am running MySQL version 4.1.18. I have a table that is used for golf round scores, with the following columns:

mem_id, the member id
comp_id,

    Requires Free Membership to View

if the round is in a competition
round_dt, date of the round
overallScore, total score for 18 holes
scoreN, one column for each of the 18 holes

When I am inserting a record, is there any way of calculating the overall score so that it can be inserted with the rest of the data? And is there any way to search this table and add all the overall scores of all the rounds played by each player in the competition and ordering them by their average score?


When a scorecard is submitted, along with the score for each of the 18 holes, doesn't the member need to submit a total score? Would you not want to input that too, and then use a query to add up the 18 scores as a cross-check? That's what I'd do.

In any case, since you are using MySQL, there is a way to do what you asked. This is non-standard, proprietary MySQL syntax:

insert into golfscores
set mem_id = 'r937'
  , comp_id = 'n'

  , round_dt = '2003-05-16'
  , score1 = 3
  , score2 = 4
  , ...
  , score18 = 5
  , overallScore = 
       score1 + score2 + ... + score18

Notice the unusual INSERT SET syntax, and the fact that you can use values set earlier in the statement to provide a value for later columns.

As for your retrieval query, that's straightforward:

select mem_id
     , sum(overallScore) / count(*) as AvgScore
  from golfscores
 where comp_id = 'xxx'
group
    by mem_id
order
    by 2 

This will give you the average score for each member who took part in competition 'xxx'. There's no danger of dividing by zero in this case, because we're only counting rows that exist. ORDER BY 2 means the second column in the result set; you could instead use the alias AvgScore or even the expression itself. The sequence is ascending, which is the way golf scores are usually shown.


This was first published in May 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: