how to decrease column values by 20 percent using mysql

Question

i have a procutd with this price: 60.33. When i increase the price to 20% i got like result 72.39 that is correct. but when i am tryng to decrease to 20% i dont have the same price that i had before. i am having 60.37 some one can help me to tell me what i have to do exactly?

This is the script sql i made to that:

round(listprice +(listprice *(20/100)),2)//Increase 20%
round(listprice -(listprice *(20/100)),2)//Decrease 20%

Answer 1

You need the reciprocal value of 20%, because you're new value isn't 120% it is the new 100% value.

round(listprice * 1.2,2) -- increase by 20%

The reciprocal for above +20% would be:

round(listprice / 1.2,2) -- decrease by 17.777777% 

Or put basically: If you want to counteract an increase of 20% then you need to reduce by 17.7777777%.

You can verify this on Windows with the calculator. Beware of the round() function!

---

Mathematics Explanation

Multiplying a value with a number is another way of adding. If you want to add 100% to a given value, then you can multiply the value with 2:

listprice * 2 

If we replace listprice with 100 then we have:

100 * 2 = 200

This is an increase of 100% compared to the starting value of 100.

If you want to increase a value by 20% then you want to add a fifth of the base value. This is the equivalent of multiplying by 1.2:

100 * 1.2 = 120

Now multiplying by 1.2 can counteracted by dividing the result by 1.2 again:

120 / 1.2 = 100

This is the equivalent of what you are looking for. You want to counteract the addition of listprice +(listprice *(20/100)).

---

Working Example

A working example can be found at db<>fiddle (MySQL 8.0)

---

Using the Original Addition

After poring over your equation last night and this morning I found a way of using your original "addition".

                20   
x = y + ( y * ----- )
               100   

This can be converted to:

                20   
x = y * ( 1 + ----- )
               100   

Which as you may note is a multiplication again. So if you were to want the original value of y, then all you need to do is to divide by original multiplicand:

                20   
y = x / ( 1 + ----- )
               100   

Or written as :

          x
     ------------
y =         20        
     (1 + ----- )  
           100

In your MySQL equation this would be:

listprice = round(listprice / (1 + (20/100)),2)

---

Working Example Using Addition

A working example can be found at db<>fiddle (MySQL 8.0)

Answer 2

UPDATE TABLE_NAME SET CALUMN_NAME = round(CALUMN_NAME * 0.80,2);