- Home /

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

Comment

Grant Fritchey

Looking forward to any answer on this. Searching 500^100 combinations for only those that sum to 500 sounds computationally intensive.

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?

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.

**Answer** by Mister Magoo
·
Aug 02, 2013 at 12:32 AM

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

**Answer** by Mister Magoo
·
Aug 02, 2013 at 12:30 AM

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

How Many SQL Server 2000 servers do you have? 15 Answers

Cannot connect to DB Engine from SSMS 2 Answers

changing "Auto Close" option 1 Answer

Number of Mirroring witnesses allow 2 Answers

Copyright 2019 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges