Database Trigger Issue. Dynamically update a column using data from multiple columns

[data888]

It appears I need a trigger of some sort to have a certain function performed. Would like to hear any suggestions on how to have this done.

 

What I need is to have data in a field populated dynamically by calculating the average of other fields within the same table.

 

Example of column names:

 

Value1, Value2, Value3, Value4, Value5, Average

 

Values 1 through 5 may be entered through a form but I need to have the average field populated by dynamically using the entered data to compute the average.

 

 

 

 

 

 

 

 

[fastsol]

It's a little confusing as to if you want this done with or without user input.  Meaning do you want the server to perform this task on it's own at certain times during the day/month/year/etc, OR is a client viewing the site supposed to view this page and have the value generated for them?  The first method you would use a cron job, which you can setup to run when ever you want and does not require a client to visit the page to run the code cause the server runs it itself.

[data888]

It's a little confusing as to if you want this done with or without user input.  Meaning do you want the server to perform this task on it's own at certain times during the day/month/year/etc, OR is a client viewing the site supposed to view this page and have the value generated for them?  The first method you would use a cron job, which you can setup to run when ever you want and does not require a client to visit the page to run the code cause the server runs it itself.

This should help you understand. You get a table with a bunch of values column (value1, value2, value3, value4, value5 and such) . It initially doesn't have an average column and looks like this:

------------------------------------------------------

| value1 | value2 | value3 | value4 | value5 |

-------------------------------------------------------

| 10000 | 15000  | 20000  | 25000 | 30000  | 

------------------------------------------------------

| 10100 | 15100  | 20100  | 25100 | 30100  |  

------------------------------------------------------

| 10200 | 15200  | 20200  | 25200 | 30200  | 

------------------------------------------------------

 

at some stage you then need to also have an 'average' column in the table to compute and store the average of the values. After adding the 'average' column, each row looks like this. All data type for both values and average =int:

 

------------------------------------------------------------------

| value1 | value2 | value3 | value4 | value5 | average |

------------------------------------------------------------------

| 10000 | 15000  | 20000  | 25000 | 30000  |     0       |

------------------------------------------------------------------

| 10100 | 15100  | 20100  | 25100 | 30100  |     0       |

------------------------------------------------------------------

| 10200 | 15200  | 20200  | 25200 | 30200  |     0       |

------------------------------------------------------------------

So what is needed is to have the average for each of the rows populated based on data in the values fields and should then look something like this:

 

------------------------------------------------------------------

| value1 | value2 | value3 | value4 | value5 | average |

------------------------------------------------------------------

| 10000 | 15000  | 20000  | 25000 | 30000  | 20000   |

------------------------------------------------------------------

| 10100 | 15100  | 20100  | 25100 | 30100  | 20100   |

------------------------------------------------------------------

| 10200 | 15200  | 20200  | 25200 | 30200  | 20200   |

------------------------------------------------------------------

 

Do you understand?

[fastsol]

Here is an example query to try.  I have not tested this example and it is strictly built off of examples I found googling.  If this doesn't work, google this sum multiple columns in a row mysql

UPDATE `table` SET `average` = ((value1 + value2 + value3 + value4 + value5) / 5)

[mac_gyver]

there's two problems with what you are asking/doing.

 

1) you should not have a database table with columns with same meaning data spread out like that. database tables are not spreadsheets. all the code and queries to store, find, update, delete, or make use of any of the values in a any column is more complicated. each same meaning piece of data should be stored in a separate row in a database table.

 

2) an average is derived data. it should not be stored, but instead calculated any time it is needed.

[data888]

 

Here is an example query to try.  I have not tested this example and it is strictly built off of examples I found googling.  If this doesn't work, google this sum multiple columns in a row mysql

UPDATE `table` SET `average` = ((value1 + value2 + value3 + value4 + value5) / 5)

Theoretically it seems like that should execute but it doesn't; if you happen to have it execute on your end feel free to discuss. This seems like it should have the capability but the thought was not to spend 5 hours researching it if someone else have utilized this in the past: http://dev.mysql.com/doc/refman/5.6/en/triggers.html

[data888]

there's two problems with what you are asking/doing.

 

1) you should not have a database table with columns with same meaning data spread out like that. database tables are not spreadsheets. all the code and queries to store, find, update, delete, or make use of any of the values in a any column is more complicated. each same meaning piece of data should be stored in a separate row in a database table.

 

2) an average is derived data. it should not be stored, but instead calculated any time it is needed.

not sure where you are going with this but what is required is that the average field be computed and stored; the issue isn't with performing calculations for display on the screen but the average data does need to be calculated and stored.

 

Each of the fields for the values column are also constantly and randomly updated and not necessarily simultaneously.

[data888]

 

Here is an example query to try.  I have not tested this example and it is strictly built off of examples I found googling.  If this doesn't work, google this sum multiple columns in a row mysql

UPDATE `table` SET `average` = ((value1 + value2 + value3 + value4 + value5) / 5)

By the way.. this finally did execute; there was an issue with the SQL string that was affecting things although there wasn't an error on the screen.