Jump to content

Archived

This topic is now archived and is closed to further replies.

tonbah

Storing AVG values

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

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
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?

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

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.