Generating A List Of Random Numbers, Summing To A Fixed Amount Using SQL

03 February 2022 - 1 answer

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(@[email protected] <[email protected])
SET @[email protected];
SET @[email protected]@sum;

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

IF(@[email protected])
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:',@[email protected]))
SET @[email protected];
END;

SET @s=0;

IF(@[email protected])
BEGIN
PRINT('****************************DONE****************************')
BREAK;
END
END;
PRINT ('end');
END;
``````