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?

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

[!--quoteo(post=352287:date=Mar 6 2006, 05:27 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 6 2006, 05:27 PM) [snapback]352287[/snapback][/div][div class=\'quotemain\'][!--quotec--] Tell us more about your database. It sounds like your design may need work. How many tables do you have? What do they represent? How are you storing the score from a single round? How are you storing user information? [/quote] I have two tables scores and users the scores table has coulmns of auto_id, ID, First name, last name, date, course, rating, slope, score, and diff the users table has columns of ID, First Name, Last name, username, password, avg_diff, and handicp Each row in the scores table represnts one round by a player

[!--quoteo(post=352268:date=Mar 6 2006, 05:10 PM:name=XenoPhage)--][div class=\'quotetop\']QUOTE(XenoPhage @ Mar 6 2006, 05:10 PM) [snapback]352268[/snapback][/div][div class=\'quotemain\'][!--quotec--] Yep, AVG() needs a GROUP BY ... Try this : [code] SELECT AVG(diff) FROM users WHERE ID=123456 GROUP BY ID; and then UPDATE users SET avg_diff = <answer from above> WHERE ID=123456; [/code] [/quote] Is there anyway to store the avg_diff and do both of these at the same time? or do I need to run one and then run the other for each user?

[!--quoteo(post=352240:date=Mar 6 2006, 04:43 PM:name=XenoPhage)--][div class=\'quotetop\']QUOTE(XenoPhage @ Mar 6 2006, 04:43 PM) [snapback]352240[/snapback][/div][div class=\'quotemain\'][!--quotec--] Code! I need code! :) Seriously, post the code you're using so we can take a crack at fixing it.. :) [/quote] Here is what I tried: SELECT scores, users UPDATE users SET avg_diff = AVG(scores.diff) WHERE scores.ID =123456 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 'UPDATE users SET avg_diff = AVG(scores.diff) WHERE scores.ID= OR SQL query: UPDATE users SET avg_diff = AVG(diff) WHERE ID =123456 MySQL said: #1111 - Invalid use of group function

I am new to php and mySQL. I have found alot of things online but am struggling with this. I am trying to create a record keeping system for my golf team. The players enter their scores on a webform and the data is stored in a mySQL database. The data while they are on the website is filtered by their user name. I can not figure out the next step. I need to take their score, subtract the Course Rating, multiply by 113 and divide by course slope. (I can do this in the mysql admin program using: UPDATE scores SET diff = (Score-Rating)*113/Slope Then I want to average all of the diff from the player and store it in the user database with their username. Then I want to take that number and mulitply it by.96 and store it in the user database as "handicp". Each time I have tried to use AVG it either tells me that I am using grouping wrong or it outputs the info but I am not able to store it. Is it possible to do this while the player is logged on to the website? Or could I do it as a series of commands in MySQL? Thanks for any help, Thomas