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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation