Jump to content

Storing AVG values


tonbah

Recommended Posts

I posted something similar ealier but now I am running into a different problem. I am creating a handicap system for golf and I am trying to make some mathematical calculations with my data. Here is what I have so far.

[code]UPDATE scores
SET diff=(score-rating)*113/Slope;

SELECT ID,AVG(diff) FROM scores
GROUP BY ID;[/code]


There are two tables witht the names scores and users the fields are as follows:
Scores: ID, score, rating, slope, diff
Users: ID, Last Name, First Name, Avg_diff, Handicp

When I run the MySQL query I get an array of the ID and avgerage of the diff column for each user. I then need to manually update each user's Avg_diff before I complete the process with

[code]UPDATE users
SET handicp=(AVG_diff*.96)[/code]

Is there anyway to store each of the AVG values to put into the Avg_diff column by ID? The more users I get the more time this query takes me.

Also What is the format for the ROUND command if I want to do only 1 decimal place?

Thanks

Thomas
Link to comment
Share on other sites

I can't aswer your question in total, but I was rounding numbers this moring and I came across this which helped me.

[code]
$number=25.66745;
echo "The number = ". $number;               // will display 25.66745
echo "<br>Rounded value of the number = ".round($number,2);     // will display 25.67
echo "<br>Rounded value of the number = ".round($number);     // will display 26
[/code]

Cheers,
Neil
Link to comment
Share on other sites


I tried it and got this:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Error
SQL query:

UPDATE users u INNER JOIN (

SELECT ID, AVG(
(
score - rating
) *113 / Slope
) AS aver
FROM scores
GROUP BY ID
)d ON d.ID = u.ID
SET u.handicp = ROUND( d.aver * .96, 1 )

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ID, AVG((score-rating)*113/Slope) as aver FROM scores GR[/quote]

If I run
[code]SELECT ID, AVG(
(
score - rating
) *113 / Slope
) AS aver
FROM scores
GROUP BY ID[/code]

Then I get the table output but it is not stored anywhere.
Any help?
Link to comment
Share on other sites

Old version of MySQL. My query requires MySQL 4.1+.

Your best option in MySQL 4.0 or less is to use a temporary table and do it in 3 queries:

[code]CREATE TEMPORARY TABLE user_averages (ID INT NOT NULL DEFAULT 0, aver FLOAT NOT NULL DEFAULT 0.0) TYPE=MyISAM

INSERT INTO user_averages (ID, aver)
    SELECT ID, AVG((score-rating)*113/Slope) FROM scores GROUP BY ID

UPDATE users u INNER JOIN user_averages a ON a.ID=u.ID SET u.handicp=ROUND(a.aver*.96,1)[/code]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.