# Generating a list of random numbers, summing to a fixed amount using SQL

This question is about generating N random numbers whose sum is M(constant) using SQL.

For example we have a number M=9.754. We need 10 random numbers whose sum is 9.754.

It should be done in SQL Server environment.

Can anybody help me?

While the @Squirrel answer is interesting but numbers here is more random here is the code:

```DECLARE @s INT=1,
@k FLOAT=0,
@final FLOAT=9.917,
@sum FLOAT =0,
@min FLOAT=1,
@max FLOAT=9.917

BEGIN
WHILE (@sum <> @final)
BEGIN
WHILE (@s <= 10)
BEGIN

SET @k =
(
SELECT ROUND(RAND(CHECKSUM(NEWID())) * (@max - @min) + @min,3)
);
PRINT (CONCAT('random: ',@k));

IF(@sum+@k <=@final)
SET @sum+=@k;
SET @max=@final-@sum;

PRINT (CONCAT('computed sum: ',@k));
IF(@max>1) SET @min=1 ELSE SET @min=0;

IF(@sum=@final)
BREAK;

SET @s = @s + 1;
SET @k = @k + 0;
END;

PRINT (CONCAT('final', @final))
PRINT (CONCAT('sum', @sum))

IF(@sum<>@final)--force stop if after 10 try the sum not match with final
BEGIN
PRINT(CONCAT('final random number:',@final-@sum))
SET @sum=@final;
END;

SET @s=0;

IF(@sum=@final)
BEGIN
PRINT('****************************DONE****************************')
BREAK;
END
END;
PRINT ('end');
END;
```
User contributions licensed under: CC BY-SA
5 People found this is helpful