using addition and division in a MySQL table row?

[shmeeg]

is it possible to use addition or division in your MySQL table for a specific row?

 

I have the rows: id, name, team, appearances, goals, assists, total, points/match, motm

 

what I want to happen is that when I add someones goals and assists, that they would be added together in the row "total" and then for the points/match column to take the value from "total" and divide it by "appearances"

 

also the points/match column I would like to use 2 decimal places, eg. 1.00, 2.33 etc

 

is this possible? if so how would I go about doing it?

[ignace]

what I want to happen is that when I add someones goals and assists, that they would be added together in the row "total" and then for the points/match column to take the value from "total" and divide it by "appearances"

 

INSERT INTO table SET goals = .., assists = .., total = goals + assists, points_per_match = total / appearances

 

This being said it's best to keep calculated data out of the database.

 

also the points/match column I would like to use 2 decimal places, eg. 1.00, 2.33 etc

 

DECIMAL(8,2) Makes for 6 digits before the . and 2 after.

[shmeeg]

so it would be easier to do the addition before it is processed in to the table?

 

also, with the INSERT INTO, is there such a thing as ADD INTO? so when statistics are being updated, if they select the player, then how many goals/assists they had in the match and it would add it to their current ones? rather than using UPDATE

[ignace]

so it would be easier to do the addition before it is processed in to the table?

 

No it would be best to keep all calculated data out of your database and store only the required values, perform the calculation in your application. The reason for this is simple, if you modify any of each value then your total is wrong (UPDATE anomaly).

 

also, with the INSERT INTO, is there such a thing as ADD INTO?

 

You can use INSERT INTO .. ON DUPLICATE KEY UPDATE ..

[Psycho]

is there such a thing as ADD INTO? so when statistics are being updated, if they select the player, then how many goals/assists they had in the match and it would add it to their current ones? rather than using UPDATE

 

That IS one of the uses for UPDATE. No need for an ADD

UPDATE table SET field = field + $newValue

 

Although, I am in agreement with ignace. Do not store calculated data. Instead create a new record for each players results from each game. You can then easily get calculated totals when you query the database.

 

Example

SELECT SUM(goals) + SUM(assists) as total GROUP BY player_id

 

Databases are for storing the 'data'. By compiling your data into totals you can prevent yourself from doing other types of calculations later. For example, what if you wanted to see what is the most points any player had in a single match?

[ignace]

Do not store calculated data.

 

The reason we advice this is to avoid an UPDATE anomaly. For example you have 3 fields: number1, number2, and total where total is the sum of number1 and number2. Now imagine I update number1 or number2 or both, total wouldn't be so accurate anymore.