I have a Total value that I need to distribute among several rows in a SQL table:
DECLARE @total numeric(38,5);
DECLARE @count int;
SET @total=123.10000
SET @count = SELECT COUNT(*) FROM mytable WHERE condition=@val;
-- let's say @count is now 3
UPDATE mytable SET my_part=@total/@count WHERE condition=@val;
--each record now has 41.03333
SELECT SUM(my_part) FROM mytable where condition = @val;
-- the sum is 123.09999, not my original 123.10000
Obviously, the original total wasn't evenly divisible by 3 so the SUM won't match the original value. And no matter what I use for scale, there will be possible divisions like this one that can't line back up.
What I would like is that one of the UPDATEd rows would contain 41.03334, and the other two would have 41.03333. I don't care which ones round up and which round down. But I care that the values can be re-summed to get the original total. Is this possible? Are there known algorithms for doing this kind of thing?