Skip to content
Advertisement

Redshift Rounding Off Issue

I have a table which has a numeric(23,2) field that I need to divide to a constant.

My baseline is this aggregation

select site, sum(sales) / 1.07 as sales from sales group by site;

But when I add another column then compare the total sum across all, I noticed some decimal drop-offs

select site, product, sum(sales) / 1.07 as sales from sales group by site, product;

Is there like a proper way to handle such in Redshift?

Advertisement

Answer

I would suggest dividing before doing the sum:

select site, product, sum(sales / 1.07) as sales
from sales
group by site, product;

Mathematically, this should be equivalent. However, because numbers are not infinite precision in computers, this may address your issue.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement