Storing AVG values

[tonbah]

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

[realjumper]

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

[wickning1]

This will set the handicap (rounded to one decimal place) in one query:

[code]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)[/code]

[tonbah]

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?

[wickning1]

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]

[Barand]

You should be able to combine the first two queries

[code]CREATE TEMPORARY TABLE user_averages
SELECT ID, AVG((score-rating)*113/Slope) AS aver
FROM scores
GROUP BY ID[/code]