---
title: "Using TSQL to select a percentage of the total sum of records at random"  
description: "Using TSQL to select a percentage of the total sum of records at random"  
author: "Mark Devid"  
published: 2013-05-06  
updated: 2013-05-06  
canonical: https://www.mindstick.com/forum/823/using-tsql-to-select-a-percentage-of-the-total-sum-of-records-at-random  
category: "mssql server"  
tags: ["mssql server"]  
reading_time: 2 minutes  

---

# Using TSQL to select a percentage of the total sum of records at random

Hi!\
\
I have a table that contains Road [reference](https://www.mindstick.com/forum/774/reference-what-does-this-error-mean-in-php) numbers and road length, with columns RoadID (int) and RoadLength (int).\
\
There are around 3000 rows. Using T-SQL I need to extract a random [selection](https://www.mindstick.com/blog/60/populate-records-in-second-listbox-according-to-the-selection-in-first-list-box) of road references and their length where the sum of the length adds up to 5% of the \
\
total length of all the roads in the table. This is for an annual road survey where roads are selected at random.\
\
I'm using T-SQL [against](https://yourviews.mindstick.com/view/81332/the-approach-of-science-against-disease-epidemics) a [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) 2008 database. Tried a few variations on triangular [queries](https://www.mindstick.com/forum/33678/sub-queries-in-sql-server) from this [article](https://www.mindstick.com/articles/95867/are-you-looking-for-500-credit-score-mortgage-lenders-houston-continue-reading-this-article) http://www.sqlservercentral.com/Forums/Topic793008\
\
-149-1.aspx but struggling with selecting [random rows](https://www.mindstick.com/forum/825/join-a-single-row-in-one-table-to-n-random-rows-in-another). I tried using order by newID() but my [results](https://yourviews.mindstick.com/story/4497/usage-of-baking-soda-magical-results) don't look correct.\
\
Any help with the most [efficient](https://www.mindstick.com/articles/33637/three-tips-for-an-energy-efficient-home-for-2019) way to do this would be appreciated. Thanks\
\
Thanks in [advance](https://www.mindstick.com/blog/33258/jee-mains-and-jee-advance-exams)!

## Replies

### Reply by AVADHESH PATEL

Hi Mark!\
Messy, but it seems to work\
--Create a temp table and add a random number columnCREATE TABLE #Roads(ROW_NUM int, RoadID int, RoadLength int)\
--Populate from zt_Roads table and add a random number fieldINSERT #Roads (ROW_NUM , RoadID , RoadLength ) (SELECT ROW_NUMBER() OVER (ORDER BY NEWID()), RoadID, RoadLength from zt_Roads)go\
--Calcualte 5% of the TOTAL length of ALL roadsdeclare @FivePercent intSELECT @FivePercent = ROUND(Sum(IsNULL((RoadLength ),0))*.01,0) from zt_Roadsprint 'One Percent of total length = ' Print @FivePercent\
--Select a random sample from temp table so that the total sample length --is no more than 5% of all roads in table; with RandomSample as (SELECT top 100 percent ROW_NUM, RoadID, RoadLength, RoadLength+ COALESCE((Select Sum(RoadLength) from #Roads b WHERE b.ROW_NUM < a.ROW_NUM),0) as RunningTotal\
From #Roads a ORDER BY ROW_NUM)\
\
Select * from RandomSample WHERE RunningTotal <@FivePercent Drop table #Roads


---

Original Source: https://www.mindstick.com/forum/823/using-tsql-to-select-a-percentage-of-the-total-sum-of-records-at-random

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.