Is it possible to automaticly have in a row a substraction between two MYSQL variables

Question

I would like to have something like this :

+----------+------+-----+--------+
| image_id | good | bad | result |
+----------+------+-----+--------+
|        1 |   10 |   2 |      x |
+----------+------+-----+--------+
|        2 |    4 |   1 |      y |
+----------+------+-----+--------+

Where x and y is calculated automaticaly to be respectively 10 - 2 and 4 - 1. (good - bad) -avoid negative number if possible-

I would like this value to change if values (good or bad) changes as well.

+----------+------+-----+--------+
| image_id | good | bad | result |
+----------+------+-----+--------+
|        1 |   10 |   2 |      x |
+----------+------+-----+--------+
|        2 |    4 |   1 |      y |
+----------+------+-----+--------+

I can do this in php but is there a way to do this directly with MYSQL ?

you can try triggers if you want column result to be updated at each good/ bad column change. — Anda Iancu, Aug 26 '13 at 14:18
I think This answers your question. http://stackoverflow.com/a/15521681/2486343 — Hyrum Hammon, Aug 26 '13 at 14:31

STT LCU · Answer 1 · 2013-08-26T14:25:06.637

use this query:

select image_id, good, bad, GREATEST(good-bad, 0) as 'result' from tbl

This will calculate the difference for each row and returns the result (or 0 if the result is negative= in another column named result.

As a general rule, try to avoid to store in columns the results of calculation based entirely on other columns of the same table, expecially if the calculations are so trivial like a simple difference.

score 1 · Answer 2 · answered Aug 26 '13 at 14:20

1

Calculate the result and return no less than zero, so avoiding negative numbers:

SELECT image_id, good, bad, GREATEST(good-bad, 0) AS result from `table`;

answered Aug 26 '13 at 14:20

nOw2

656
7
13

This works, but the question was to store the value in the database automatically not to compute it when it's needed – Hyrum Hammon Aug 26 '13 at 14:53
It sounds to me like you want a column whose value depends on the values of others. Is this the case or do you really just want the value in your query? – Hyrum Hammon Aug 27 '13 at 14:25

score 0 · Answer 3 · answered Aug 26 '13 at 14:18

0

You can simply write:

select image_id, good, bad, (good-bad) as result from mytable

answered Aug 26 '13 at 14:18

Ohlin

4,068
2
29
35

Benoit · Answer 4 · 2013-08-26T14:49:06.203

0

What you could do is have this schema:

CREATE TABLE tbl (image_id INTEGER PRIMARY KEY, good INTEGER, bad INTEGER);
CREATE VIEW tbl_result AS SELECT image_id, good, bad, CAST(good AS INTEGER) - bad AS result FROM tbl;

edited Aug 26 '13 at 14:49

answered Aug 26 '13 at 14:19

Benoit

76,634
23
210
236

thank it's maybe what I'm looking for. But I have "tinyint unsigned" so i got an error :p : "ERROR 1690 (22003): BIGINT UNSIGNED value is out of range" (I have a lot of 0 - 0 by the way) – bob dylan Aug 26 '13 at 14:27
@bobdylan: now it should cast to an Integer. – Benoit Aug 26 '13 at 14:49
I changed it a little bit : CREATE VIEW tbl_result AS SELECT image_id, good, bad, IF(good >= bad, good - bad, 0) AS result FROM tbl; – bob dylan Aug 26 '13 at 14:55

Is it possible to automaticly have in a row a substraction between two MYSQL variables

4 Answers4