Ad

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

- 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?

Ad

Answer

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;
Ad
source: stackoverflow.com
Ad