Pattern

Allocation with a remainder

Spread an amount over rows by weight, rounded to the cent, with the remainder on one chosen row so the parts add up exactly.

Share = ROUND(amount × weight / SUM(weight) OVER (PARTITION BY parent), 2). Rounding each share loses or adds a cent: give amount − SUM(share) OVER (PARTITION BY parent) to one row picked by a fixed rule (the biggest, then the smallest id). Test it: per parent, the shares add up to the amount. Used for fees, shipping, order discounts, budgets.

WITH lines(line_no, amount) AS (VALUES (1, 10.00), (2, 10.00), (3, 10.00)),
shares AS (
  SELECT line_no, amount,
         CAST(ROUND(1.00 * amount / SUM(amount) OVER (), 2) AS DECIMAL(10,2)) AS rounded,
         ROW_NUMBER() OVER (ORDER BY amount DESC, line_no) AS rank_in_order
  FROM lines
)
SELECT line_no, rounded,
       rounded + CASE WHEN rank_in_order = 1 THEN 1.00 - SUM(rounded) OVER () ELSE 0 END AS fee_share
FROM shares
ORDER BY line_no;

line_no	rounded	fee_share
1	0.33	0.34
2	0.33	0.33
3	0.33	0.33

Try it in Randori

Practise:Spread the fee over the lines