CODEWITHSUNDEEP
mysqlsql
Shoxxer
Shoxxer

Reputation: 35

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

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.

Upvotes: 0

Views: 52

Answers (2)

ktbird7
ktbird7

Reputation: 56

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.

Upvotes: 1

Brandon
Brandon

Reputation: 10038

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?

Upvotes: 1

Related Questions