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;

​x
 
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;

 
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?

Answer

I would suggest dividing before doing the sum:

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

 
select site, product, sum(sales / 1.07) as salesfrom salesgroup by site, product;​

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