CODEWITHSUNDEEP
sql-serverrandom
user1753385
user1753385

Reputation: 105

Select x random number having sum n in Sql Server

How do i generate x random number to have sum=n example:

N=5000 x=5
1- 1500
2- 500
3- 700
4- 1400
5- 900

Upvotes: 4

Views: 550

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17146

Try this query:

DECLARE @x int, @N int, @num int
SET @X=5
SET @N=5000

CREATE TABLE #temp (chosen int)

WHILE @N>=0 and @X>0
BEGIN
    SET @num =RAND()*@N
    INSERT INTO #temp (chosen)
    SELECT CASE WHEN @X=1 THEN @N ELSE @num END AS Chosen
    SET @N=@N-@num
    SET @X=@X-1

END
SELECT chosen FROM #temp
DROP TABLE #temp

Upvotes: 3

GEEK
GEEK

Reputation: 57

Please refer this, this might help you.

https://ask.sqlservercentral.com/questions/104878/how-can-i-generate-random-numbers-that-sum-will-be.html

Upvotes: 1

Related Questions