MadScientist MadScientist - 6 months ago 7
MySQL Question

Mysql Mathematics on the fly

I am doing a migration from one database structure to another where there are rating values in a column.

In database table A the rating is 0-10 in database table B the rating is from 0-4.

I would like to select all values from TABLE A and convert the value from a 0-10 to a 0-4 EG. if the value in A is 5 then it would become 2 and so forth. The resulting value would need to be a single decimal place.

Thanks

Answer

So you calculate the factor is 10 / 4 = 2.5

So if column A are integer you will have 1 decimal

 SELECT columnA / 2.5
 FROM TableA

otherwise

 SELECT CAST( columnA / 2.5 AS DECIMAL (2,1) )
 FROM TableA
Comments