Rounding currency to add up to the total value

Question

I have a sales tax value that I need to divide evenly across 3 items. For example: $153.88/3 = 51.29333333333333

When rounded to 2 decimal places to represent currency, it = $51.29. However $51.29*3=$153.87 which is 1 cent off from the total tax amount.

Is there a function to solve for these rounding errors so that the individual values always add up to the total? So that extra 1 cent is randomly allocated to 1/3 items?

select '1' as item_number, 153.88 as sales_tax, round(153.88 /3,2) as rounded
union all
select '2' as item_number, 153.88 as sales_tax, round(153.88 /3,2) as rounded
union all
select '3' as item_number, 153.88 as sales_tax, round(153.88 /3,2) as rounded
     

Answer 1

This is a tricky problem. It is simple enough to use floor(), ceil(), or round() to get an approximate solution. You can then even add the "extra" to one of the rows.

However the extra might be more than 0.01 and that starts to looks awkward. This doesn't happen when you have three items, but it can happen with more. I prefer to distribute these evenly over the rows.

So, I recommend:

with t as (
      select '1' as item_number, 153.89 as sales_tax
      union all
      select '2' as item_number, 153.89 as sales_tax
      union all
      select '3' as item_number, 153.89 as sales_tax
      union all
      select '4' as item_number, 153.89 as sales_tax
      union all
      select '5' as item_number, 153.89 as sales_tax
      union all
      select '6' as item_number, 153.89 as sales_tax
      union all
      select '7' as item_number, 153.89 as sales_tax
)
select t.*,
       (floor(sales_tax * 100 / count(*) over ()) +
        (case when floor(sales_tax * 100 / count(*) over ()) * count(*) over () +
                   row_number() over ()  - 1 < sales_tax * 100
              then 1 else 0
         end)
       ) / 100.0
from t;

I've included an example where the distribution makes a difference.