I have cent values, retained as integers, that I need to convert to dollars and show them as a decimals.


A value is kept as 1925, so I need to be able to return 19.25.

This is what I had been doing in MySQL

format(price/ 100, 2) as amount

and when summing the value, I use

cast(SUM(price) / 100 AS DECIMAL(12, 2)) as total_amount

These seem to work fine, but when moving over to Redshift, I am not sure if I am using the right function.

Current attemtp

In Redshift, I try with

price :: DECIMAL(12, 2) as amount

and then

SUM(price)/ 100 :: DECIMAL(12, 2) as total_amount

In both instances, I get 4 decimal points in my results? Is using wrapping these functions with trunc(,2) a good way to solve this?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.