MadScientist MadScientist - 2 years ago 94
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.


Answer Source

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


 SELECT CAST( columnA / 2.5 AS DECIMAL (2,1) )
 FROM TableA
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download