How can I get around a column in a single SQL query without changing the total?

Question

I have a table defined as follows:

create table #tbFoo
(bar float)

And I'm looking for a way to round every value contained in a column without changing the total amount (which, as you know, is an integer or very close to an integer due to the precision of the floating point number).

Rounding each value to the nearest integer will not work (for example: 1.5; 1.5 will be rounded to 1; 1 or 2; 2)

This is quite easy to do using a few queries (for example, storing the original amount, rounding, calculating the new amount and updating as many lines as necessary to return to the original amount), but this is not a very elegant solution.

Is there a way to do this using a single SQL query?

---

I am using SQL Server 2008, so welcome solutions using this particular provider.

---

Edit: I'm looking for a query that minimizes the differences between old values ​​and new ones. In other words, the value should never be rounded if the larger value is rounded down and vice versa

Answer 1

Update:

See this solution, which is described in more detail in an article on my blog:

• The rounding number that stores their amount

---

You need to save the cumulative bias for each value:

1.2   (1 + 0.0)  ~ 1    1   1.2   +0.2
1.2   (1 + 0.2)  ~ 1    2   2.4   +0.4
1.2   (1 + 0.4)  ~ 1    3   3.6   +0.6
1.2   (1 + 0.6)  ~ 2    5   4.8   -0.2
1.2   (1 - 0.2)  ~ 1    6   6.0   0.0

This is easy to do in MySQL, but in SQL Serveryou will have to write a cursor or use cumulative subqueries (which are less efficient).

Update:

.

(N) , .

(, ) N , .

SELECT  value,
        FLOOR(value) + CASE WHEN ROW_NUMBER() OVER (ORDER BY value - FLOOR(value) DESC) <= cs THEN 1 ELSE 0 END AS nvalue
FROM    (
        SELECT  cs, value
        FROM    (
                SELECT  SUM(value) - SUM(FLOOR(value)) AS cs
                FROM    @mytable
                ) c
        CROSS JOIN
                @mytable
        ) q

script :

SET NOCOUNT ON
GO
SELECT  RAND(0.20090917)
DECLARE @mytable TABLE (value FLOAT NOT NULL)
DECLARE @cnt INT;
SET @cnt = 0;
WHILE @cnt < 100
BEGIN
        INSERT
        INTO    @mytable
        VALUES  (FLOOR(RAND() * 100) / 10)
        SET @cnt = @cnt + 1
END

INSERT
INTO    @mytable
SELECT  600 - SUM(value)
FROM    @mytable

Answer 2

n , (+ -0,5), + - (n * 0,5). 6 , , , 3, .

- 10.2 11, , + -0,5 + -0,8, ?

( ), . , .

. 3 1/3 , , 33, 33 33. - , , + -0,5 . 100%, ( )

, , float , 0,1.

Answer 3

First, get the difference between the rounded amount and the actual amount and the number of entries:

declare @Sum float, @RoundedSum float, @Cnt int

select @Sum = sum(bar), @RoundedSum = sum(round(bar)), @Cnt = count(*)
from #tbFoo

Then you distribute the difference equally to all values ​​before rounding:

declare @Offset float

set @Offset = (@Sum - @RoundedSum) / @Cnt

select bar = round(bar + @Offset)
from #tbFoo