- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

Hi, I am trying to generate random numbers for several rows where numbers are between x and y but total sum of those numbers will be z. For example, generate 100 random numbers where sum of all of them would be 5000. Thanx

sql-server-2008
Comment

Those are excellent answers. You should vote on them. With all the helpful answers below you should indicate them by clicking on the thumbs up next to them. If any one of the answers helped you solve your problem, click on the check box next to that answer.

Does it have to be exactly the count of numbers you request as well as the sum ,e.g. 100 numbers totalling 5000? or is it just that you want any count of numbers totalling the requested amount?e.g. 98 numbers totalling 5000? Can you have repeated numbers?

Ok, assuming you don't want to specify the count and do want unique numbers, here is a proc to do it create proc [dbo].[GenerateNumberListToTotal] @start int -- smallest allowable number , @end int -- largest allowable number , @target int -- target number - all selected numbers must total this amount , @iterationTime int=2 -- how long to hunt for the total in each attempt , @runTime int=20 -- how long overall to hunt for a solution as /* Written 2nd August 2013 Mister Magoo Purpose : To select a pseudo-random set of unique numbers from within a limited pool, which SUM to the requested total. Edit: 08/10/13 changed identity to start at 0 instead of 1 Edit: 08/10/13 changed random number generator to be newid() based Edit: 08/10/13 removed redundant code */ set nocount on; -- Create a temporary table to store the results if object_id('tempdb..#numberList') is not null drop table #numberList; -- it uses the identity column as a running total -- and has a primary key on Number to make checks quick create table #numberList(Number int primary key, Total int identity(0,1)); -- set things up to allow us to insert into the identity column set identity_insert #numberList on; -- End temporary table code -- local variables declare @range int -- holds the scope of allowable numbers = @end - @start + 1 , @endtime datetime -- used in the main loop to prevent searches taking too long , @finishtime datetime; -- used to limit the time spent overall searching for a solution -- calculate the scope of the number range set @range = @end - @start + 1; -- calculate when we must stop searching set @finishtime = dateadd(second,@runTime,getdate()) -- Main loop tests to see if a solution has been found -- or if we have run out of time and exits if either is true while isnull((select sum(Number) from #numberList),0) @target and @finishtime > getdate() begin -- now empty the temporary table truncate table #numberList; -- prime the table with zeros insert #numberList(Number, Total) values(0,0); -- calculate when we must abandon this iteration of the search set @endtime = dateadd(second,@iterationTime,getdate()) -- This one statement WHILE loop does all the work of finding a solution -- It will loop until a solution is found or it has run out of time while ISNULL(SCOPE_IDENTITY(), 0) < @target and @endtime > getdate() insert #numberList(Number, Total) select a.number , ISNULL(SCOPE_IDENTITY(),0) + a.number from -- this next line calculates a new pseudo-random number -- within the allowed number range -- but also limited to not be higher than the remaining -- required value - as there is no need to look for a number -- which would take us over the total required ( values( @start + ( abs( checksum( newid() ) ) % @range ) % ( @target - SCOPE_IDENTITY() ) ) ) as a(number) where a.number0 and not exists (select 1 from #numberList as nl where nl.Number = a.number) ; end -- only return a result set if we succeeded in matching the target value if (select sum(Number) from #numberList) = @target select Number, Total from #numberList where Number0 -- ignore the "seed" value order by total; Call it like this: exec GenerateNumberListToTotal @start = 1 -- smallest allowable number , @end = 120 -- largest allowable number , @target = 5000 -- target number - all selected number must total this amount , @iterationTime = 5 -- how long to hunt for the total in each attempt , @runTime = 15 -- how long overall to hunt for a solution

If you want to allow duplicate numbers in the results, then this version will do it create proc GenerateNumberListToTotalWithDupes @start int -- smallest allowable number , @end int -- largest allowable number , @target int -- target number - all selected numbers must total this amount , @iterationTime int=2 -- how long to hunt for the total in each attempt , @runTime int=20 -- how long overall to hunt for a solution as /* Written 2nd August 2013 Mister Magoo Purpose : To select a pseudo-random set of unique numbers from within a limited pool, which SUM to the requested total. Edit: 08/10/13 changed identity to start at 0 instead of 1 Edit: 08/10/13 changed random number generator to be newid() based Edit: 08/10/13 removed redundant code */ set nocount on; -- Create a temporary table to store the results if object_id('tempdb..#numberList') is not null drop table #numberList; -- it uses the identity column as a running total -- and has a primary key on Number to make checks quick create table #numberList(Number int, Total int identity(0,1)); -- set things up to allow us to insert into the identity column set identity_insert #numberList on; -- End temporary table code -- local variables declare @range int -- holds the scope of allowable numbers = @end - @start + 1 , @endtime datetime -- used in the main loop to prevent searches taking too long , @finishtime datetime; -- used to limit the time spent overall searching for a solution -- calculate the scope of the number range set @range = @end - @start + 1; -- calculate when we must stop searching set @finishtime = dateadd(second,@runTime,getdate()) -- Main loop tests to see if a solution has been found -- or if we have run out of time and exits if either is true while isnull((select sum(Number) from #numberList),0) @target and @finishtime > getdate() begin -- now empty the temporary table truncate table #numberList; -- prime the table with zeros insert #numberList(Number, Total) values(0,0); -- calculate when we must abandon this iteration of the search set @endtime = dateadd(second,@iterationTime,getdate()) -- This one statement WHILE loop does all the work of finding a solution -- It will loop until a solution is found or it has run out of time while ISNULL(SCOPE_IDENTITY(), 0) < @target and @endtime > getdate() insert #numberList(Number, Total) select a.number , ISNULL(SCOPE_IDENTITY(),0) + a.number from -- this next line calculates a new pseudo-random number -- within the allowed number range -- but also limited to not be higher than the remaining -- required value - as there is no need to look for a number -- which would take us over the total required ( values( @start + ( abs( checksum( newid() ) ) % @range ) % ( @target - SCOPE_IDENTITY() ) ) ) as a(number) where a.number0 end -- only return a result set if we succeeded in matching the target value if (select sum(Number) from #numberList) = @target select Number, Total from #numberList where Number0 -- ignore the "seed" value order by total; Call it like this exec GenerateNumberListToTotalWithDupes @start = 1 -- smallest allowable number , @end = 120 -- largest allowable number , @target = 5000 -- target number - all selected number must total this amount , @iterationTime = 5 -- how long to hunt for the total in each attempt , @runTime = 15 -- how long overall to hunt for a solution

**19** People are following this question.

Copyright 2022 Redgate Software.
Privacy Policy