how do I stop Athena from rounding during calculations

Hello,

Greetings of the day!

I understand that you are seeing unexpected rounding off of your data in Athena when you were computing some data out of your decimal typed columns. Please note that, in Athena whenever you perform computation between two columns of different types then if the computation is compatible then the output would be cast into the higher datatype with higher precision. By this I mean that, when you performed the arithmetic operation between the decimal(13,3) and decimal(5,0) datatypes then the output would be coming up as the type of decimal(13,3). Now if the output is going beyond this precision then inherently it would be rounded up to fit it to decimal(13,3).

Hence, as you mentioned 0.002 X 40 = 0.080, 0.080 / 47 = 0.00170212765957... (I think is being rounded to 0.002), it is indeed being rounded up to 0.002. I have tested it on my test environment as well. As per the inherent logic 0.00170212765957 must be able to fit in decimal(13,3) but it is going beyond this thereby it is being rounded off to the nearest value which is 0.002.

You can try increasing the scale value of your decimal type to a higher number. Instead of decimal(13,3), I had tried with decimal(13,6) and it was able to give me value without much rounding off. Therefore, it might be better to set the decimal column to the required precision and scale.

Hope this helps! Thanks!