CODEWITHSUNDEEP
mysqlsql
Julia Surikova
Julia Surikova

Reputation: 15

How to change a table (create/alter) so that the calculated “Average score” field is shown when querying the entire table without using INSERT, UPDATE

I have a table Marks. I want to add a column Average_score which will be calculated like (Math+Physics)/2 How can I do that without update and insert?

Select *, (Math+Physics)/2 from Marks

Isn't the right answer, I need to modify the structure.

Table Marks

Upvotes: 0

Views: 35

Answers (1)

forpas
forpas

Reputation: 164174

Add a new generated column to your table like this:

alter table Marks add column `Average Score` float 
generated always as ((Math + Physics) / 2) virtual;

You can change the column to be stored and not virtual.
See the demo.
Results:

| N   | Surname | Name | Math | Physics | Average Score   |
| --- | ------- | ---- | ---- | ------- | --------------- |
| 1   | Smith   | Ann  | 5    | 4       | 4.5             |
| 2   | Perkins | Zoe  | 2    | 3       | 2.5             |
| 3   | Jones   | Rick | 5    | 5       | 5               |

More about generated columns here:
CREATE TABLE and Generated Columns and ALTER TABLE and Generated Columns

Upvotes: 2

Related Questions