Create a column in Mysql that automatically divides the other two column values

Asked

Viewed 7,215 times

2

People need to explain to me how I can make it similar to what happens in Excel I can create a column in Mysql that divides the values of the other two columns. Something like column 1 / column 2= column 3.

For example I have two columns and several rows, in the first row in column 1 I would have value 4, in the first row in column 2 I would have value 2, in the first row of column 3 would be value 4/2

mysql sql

  • I couldn’t understand it. Give some example

  • For example I have two columns and several rows, in the first row in column 1 I would have value 4, in the first row in column 2 I would have value 2, in the first row of column 3 would be value 4/2.

  • Okay, put that in the question. You can edit. Another thing, what do you want is an SQL code that does this? Or something else? (put that in the question too)

  • SELECT field1, field2, field1/field2 AS Division FROM fieldName

2 answers

5


From version 5.7 it is possible to create calculated columns. The (simplified) syntax is as follows: 

col_name data_type [GENERATED ALWAYS] AS (expression)

Applied to your case would be so: 

CREATE TABLE tabela (
   campo1 DOUBLE,
   campo2 DOUBLE,
   divisao DOUBLE AS (campo1/campo2)
);

If you use a previous version there are 3 possible solutions:

1 - Do the calculation in SELECT:

In the indication of the columns that this should return, other can be defined that are calculated according to the columns of the table. 

SELECT campo1, campo2, campo1/campo2 AS nomeQueQuiserDar FROM tabela

With the clause AS can give any name you want to the calculated result.

See on Sqlfiddle

2 - Create a VIEW with the calculated field:

CREATE VIEW nome_view AS
SELECT campo1, campo2, campo1/campo2 AS divisao FROM tabela;

Use the VIEW instead of the SELECT:

SELECT campo1, campo2, divisao FROM nome_view

3 - Create a TRIGGER to calculate the result and store in a table column: 

CREATE TRIGGER nome_trigger AFTER INSERT
ON tabela
FOR EACH ROW SET NEW.campo3 = NEW.campo1 / NEW.campo2

You must create the field3 in the table before creating the TRIGGER.

ALTER TABLE tabela ADD campo3 DOUBLE

If the table already has data, before creating the TRIGGER, update field 3 according to existing data.

UPDATE tabela SET campo3 = campo1/campo2

If the values of field 1 and field 2 can be changed, it will be necessary to create another TRIGGER to reflect this change in field3.

CREATE TRIGGER update_trigger BEFORE UPDATE
ON tabela
FOR EACH ROW SET NEW.campo3 = NEW.campo1 / NEW.campo2

  • At TRIGGER I have to create column3 before?

  • Yes, the column should exist, Trigger only does the work of calculating the column.

  • The value is set to NULL

  • I added more details to the answer.

0

It would also be possible to create a calculated column, for example:

Create table ( campo1 double, campo2 double, campo3 as ((campo1/campo2) as double));

This type of table can interfere with processing so check another type of table persisted...

I hope I’ve helped.

  • The question refers to Mysql where only from version 5.7 it is possible to create calculated columns. Also note that the syntax you used is not Mysql.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.