Sum total of several MySQL columns stored in another column?

Question

I have several columns in a MySQL database that I would like to add and store in another column:

column1     column2     column3    subtotal
10          10          10         30

Is there a way to accomplish this?

Answer 1

update yourtable set subtotal = col1 + col2 + col3

Answer 2

If you simply update subtotal, you have to maintain its value - ie every time one of the other columns is updated, your code has to remember to update subtotal too.

There are two ways to address this issue:

1. Create a trigger that fires when the row is updated. This option requires database "kung fu" and isn't immediately obvious to anyone else looking at the table the the trigger exists
2. Create a VIEW that does the calculation

I think option 2 is the best approach.

Here's how you code a view.

create view mytable_totalled as
select col1, col2, col3,  col1 + col2 + col3 as subtotal
from mytable

Once created, the view can be used just like a table (with a few caveats), but you can certainly do any kind of select on it. The new column is calculated on the fly when selected