CODEWITHSUNDEEP
mysqlsql
Aphex22
Aphex22

Reputation: 93

How to create a column in MYSQL which automatically SUMs the values from other columns within the same row

Could anybody tell me whether it is possible to have a column within a table in MYSQL that automatically performs the SUM function for a given number of columns.

As a comparative example in Microsoft Excel, it's possible to have a cell that performs the SUM function for a given range of cells and automatically updates i.e. (=SUM E4:E55)

Is it possible to have a column which achieves the same function in MYSQL?

To further elaborate -

I have numerous columns relating to the quantity of different sizes of our products i.e. quantity_size_* and wanted a column that would SUM the value of the quantity columns and update automatically if any of the values are changed.

Any advice would be great. Thanks

Upvotes: 0

Views: 302

Answers (3)

juergen d
juergen d

Reputation: 204746

Normally you would do that in your select query on-the-fly and don't store those calculation. 

select some_column,
       col1 * col2 as some_calculation_result
from your_table

But if you have a really good reason not to do it that way then you can use a trigger to calculate those data.

You need an update trigger to catch changes in the data and an insert trigger to calculate on insertion.

An example of an insert trigger goes like this

delimiter |
CREATE TRIGGER sum_trigger AFTER INSERT ON your_table 
FOR EACH ROW BEGIN
    SET NEW.sum_column = NEW.column1 * NEW.column2;          
END
|
delimiter ;

Upvotes: 2

Gooner
Gooner

Reputation: 256

Mysql also heve the SUM() function which you can use in a select query. So the best option will be to use a SELECT SUM() query or else you can even create a VIEW for that , but triggers
are not recommended as they may become troublesome.

Upvotes: -1

Mark Wagoner
Mark Wagoner

Reputation: 1769

I think you will have to do this with a trigger. A column by itself just store data, it can't do things programmatically.

Upvotes: 0

Related Questions