make new column with math function in table

Question

id    data1        data2       
1      10          15           
2      10          20

I have this table, and I want to add new column (point) in this table.

id     data1      data2        point
1      100         105           5
2      100         200          100

I'd like to add this point value by calculating this formula as follows:

point = (data2 - data1)/data1 * 100

How can I add this value into point column?

Please let me know. Thanks

Possible duplicate of [Column calculated from another column?](http://stackoverflow.com/questions/5222044/column-calculated-from-another-column) — Ed Gibbs, May 23 '13 at 18:19

score 2 · Answer 1 · answered May 23 '13 at 18:23

2

Why not just use a VIEW

CREATE VIEW vw_Table1 AS
SELECT id, data1, data2, (data2 - data1)/data1 * 100 point
  FROM table1

answered May 23 '13 at 18:23

peterm

score 1 · Answer 2 · answered May 23 '13 at 18:18

You can use a trigger to keep it updated as @Yogendra Singh said, but if its a one-time thing then you can do it in a single UPDATE statement:

ALTER TABLE data ADD COLUMN point integer;
UPDATE data SET point = (data2 - data1)/data1 * 100;

The trigger function would look like:

CREATE TRIGGER update_point BEFORE INSERT ON data
  FOR EACH ROW BEGIN
    SET NEW.point = (data2 - data1)/data1 * 100;
  END;

score 0 · Answer 3 · answered May 23 '13 at 18:17

0

I think you can use a after insert/update trigger to update the point column after inserting/updating the values in the data1 and data2 columns.

You may want to refer Creating Triggers in MySQL for more details.

answered May 23 '13 at 18:17

Yogendra Singh

3 Answers3