Q

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, 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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close