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

Question

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?

Answer 1

Interesting requirement.

Query below uses tally table / number table to generate 10 random numbers after that find the ratio. Final query check for case where sum of the numbers is not equal to @m and make the adjustment on the biggest number.

declare @m decimal(10,3) = 9.754,
        @n int           = 10;

with 
-- using recursive CTE to generate a number table
numbers as
(
    select  n   = 1
    union all
    select  n   = n + 1
    from    numbers
    where   n   < @n
),
-- generate random positive numbers using newid()
-- Note : 100 is chosen arbitrary 
num as
(
    select  n = abs(checksum(newid())) % 100
    from    numbers
),
-- calculate the ratio
ratio as
(
    select  r, 
            rn     = row_number() over (order by r desc),
            sum_r  = sum(r) over()
    from
    (
        select  r = convert(decimal(10,3), n * 1.0 / sum(n) over() * @m)
        from    num
    ) r
)
-- sum(r) may not equal to @m due to rounding
-- find the difference and adjust it to the biggest r
select  r, rn, sum_r,
        adj_r     = r + case when rn = 1 then @m - sum_r else 0 end,
        sum_adj_r = sum(r + case when rn = 1 then @m - sum_r else 0 end) over()
from    ratio

dbfiddle demo