Jump to content

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


Recommended Posts

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.

 

 

 

 

 

 

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.