johnnyg123
asked on
randomly group sql query results based on percentage
I asked a similar question awhile back but there is an interesting twist now
Here is a sample of data from my Person table (sql server 2008)
FirstName LastName City State MailTo
Al Doe Dallas TX Y
Jill Doe Dallas TX Y
Jane Doe Austin TX Y
John Smith Houston TX Y
Ron White New York NY Y
Beth Doe Los Angeles CA Y
Bart Doe Rochester NY Y
Jim Jones Burbank CA Y
Jack Black Annaheim CA Y
Fred Flintstone Bedrock TX Y
I need to divide the results of a query by some criteria (for this example we will use state)
some times I might need to 'randomly' place the results into 1 of 3 groups (33%)
Sometimes 1 of 2 groups (50%) and so on. (Will always be based on percentage)
Using the example data above,
lets say I need 2 groups(50%) for all people in TX (may not be exactly evenly sized groups)
Group1
Al Doe Dallas TX Y
Jane Doe Austin TX Y
Jill Doe Dallas TX Y
Group2
John Smith Houston TX Y
Fred Flintstone Bedrock TX Y
lets say I need 3 groups(33%) for all people in TX (may not be exactly evenly sized groups)
Group1
Al Doe Dallas TX Y
Jill Doe Dallas TX Y
Group2
John Smith Houston TX Y
Fred Flintstone Bedrock TX Y
Group 3
Jane Doe Austin TX Y
Trying to figure out best way to do this
My example list just one criteria but could be multiple like state and last name, etc
Any ideas????
Here is a sample of data from my Person table (sql server 2008)
FirstName LastName City State MailTo
Al Doe Dallas TX Y
Jill Doe Dallas TX Y
Jane Doe Austin TX Y
John Smith Houston TX Y
Ron White New York NY Y
Beth Doe Los Angeles CA Y
Bart Doe Rochester NY Y
Jim Jones Burbank CA Y
Jack Black Annaheim CA Y
Fred Flintstone Bedrock TX Y
I need to divide the results of a query by some criteria (for this example we will use state)
some times I might need to 'randomly' place the results into 1 of 3 groups (33%)
Sometimes 1 of 2 groups (50%) and so on. (Will always be based on percentage)
Using the example data above,
lets say I need 2 groups(50%) for all people in TX (may not be exactly evenly sized groups)
Group1
Al Doe Dallas TX Y
Jane Doe Austin TX Y
Jill Doe Dallas TX Y
Group2
John Smith Houston TX Y
Fred Flintstone Bedrock TX Y
lets say I need 3 groups(33%) for all people in TX (may not be exactly evenly sized groups)
Group1
Al Doe Dallas TX Y
Jill Doe Dallas TX Y
Group2
John Smith Houston TX Y
Fred Flintstone Bedrock TX Y
Group 3
Jane Doe Austin TX Y
Trying to figure out best way to do this
My example list just one criteria but could be multiple like state and last name, etc
Any ideas????
ASKER
Hey Scott
Thanks so much for the response
Is there a random component to ntile?
When I run it the groups seem to be the same entry
Thanks so much for the response
Is there a random component to ntile?
When I run it the groups seem to be the same entry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Actually,
I thought about it and this seems like it will work perfect
SELECT [FirstName]
,[LastName]
,[City]
,[State]
,[Mailto]
,[MailtoUpdate]
,NTILE(3) OVER (order by newid()) as NTILE
FROM [MarketingDirectMailLists] .[dbo].[Ma ilList] where state = 'tx'
Thanks!
I thought about it and this seems like it will work perfect
SELECT [FirstName]
,[LastName]
,[City]
,[State]
,[Mailto]
,[MailtoUpdate]
,NTILE(3) OVER (order by newid()) as NTILE
FROM [MarketingDirectMailLists]
Thanks!
You're welcome! Glad it helped.
NTILE(3) would be 33% in each ntile (group); NTILE(2) would be 50%; etc.