How to create query with simple formula?

Question

Hey is there any way to create query with simple formula ? I have a table data with two columns value_one and value_two both are decimal values. I want to select this rows where difference between value_one and value_two is grater then 5. How can i do this?

Can i do something like this ?

SELECT * FROM data WHERE (MAX(value_one, value_two) - MIN(value_one, value_two)) > 5

Example values

value_one, value_two
1,6
9,3
2,3
3,2

so analogical difs are: 5, 6, 1, 1 so the selected row would be only first and second.

Answer 1

Consider an example where smaller number is subtracted with a bigger number:

2 - 5 = -3

So, the result is a difference of two numbers with a negation sign.

Now, consider the reverse scenario, when bigger number is subtracted with the smaller number:

5 - 2 = 3

Pretty simple right.

Basically, the difference of two number remains same, if you just ignore the sign. This is in other words called absolute value of a number.

Now, the question arises how to find the absolute value in MySQL? Answer to this is the built-in method of MySQL i.e. abs() function which returns an absolute value of a number.

ABS(X): Returns the absolute value of X.

mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32

Therefore, without worrying about finding min and max number, we can directly focus on the difference of two numbers and then, retrieving the absolute value of the result. Finally, check if it is greater than 5.

So, the final query becomes:

SELECT *
  FROM data
  WHERE abs(value_one - value_two) > 5;

You can also do complex operations once the absolute value is calculated like adding or dividing with the third value. Check the code below:

SELECT *
      FROM 
           data
      WHERE 
           (abs(value_one - value_two) / value_three) + value_four > 5;

You can also add multiple conditions using logical operators like AND, OR, NOT to do so. Click here for logical operators.

SELECT *
          FROM 
               data
          WHERE 
               ((abs(value_one - value_two) / value_three) + value_four > 5)
                AND (value_five != 0);

Here is the link with various functions available in MySQL: https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

Answer 2

No, you would just use a simple where clause:

select *
from data
where abs(value_one - value_two) > 5;