Is it possible to add an column that has the average value from the other columns?

Question

I'm wondering if it is possible to have a column that has the AVG value of the other columns of the same table.

For example:

Columns:   ID 125Hz 250Hz 500Hz 750Hz 1000Hz 1500Hz 2000Hz 3000Hz 4000Hz 6000Hz 8000Hz AVG
Values:    1  92    82     63    83    32     43     54     56     54     34      54    50

Can it calculate the average of all the other columns and put the average value automatically on the AVG column? I am using PHPMyAdmin.

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

It's my understanding that MySQL doesn't have computed columns. Somebody correct me if I am wrong.

Therefore, you essentially need this statement.

UPDATE [table_name] SET [AVG] = (125Hz + 250Hz + 500Hz + 750Hz + 1000Hz + 1500Hz + 2000Hz + 3000Hz + 4000Hz + 6000Hz + 8000Hz ) / 11;

For each row, add up the column values and divide by 11 because there are 11 such columns. It's not the most elagent because you need to hard-code 11, but it's better than nothing.

You could put this into a trigger on the table so it updates each row when it changes. I think that will get you what you need.

A similar question and answer was asked and the trigger there may be helpful for you: Column calculated from another column?

edited May 23 '17 at 11:49

Community

1
1

answered Jun 25 '14 at 15:34

Brandon

9,822
3
27
37

This didnt work as a trigger. Do you know how the code would look be fit as a trigger? – Shoxxer Jun 25 '14 at 21:29
I haven't worked with MySQL enough to show how to do it. You say it didn't work. What was the problem? – Brandon Jun 26 '14 at 12:33

score 1 · Answer 2 · answered Jun 25 '14 at 15:35

1

You can't put this in your original table but you can create a view to accomplish this:

CREATE VIEW column_avg AS
SELECT (125Hz + 250Hz)/2 AS total_avg
FROM table_name;

Just list all of the columns in the addition part and divide by the number of columns.

answered Jun 25 '14 at 15:35

ktbird7

56
8

This is the average of the averages of the averages - is that what you meant? – Brandon Jun 25 '14 at 15:37
I thought that's what they meant but seems I was incorrect, updated – ktbird7 Jun 25 '14 at 15:40

Is it possible to add an column that has the average value from the other columns?

2 Answers2