vendredi 1 février 2019

SQL Query for Random 10% with a minimum of 20 rows

I have been tasked with generating a report that will randomly pick 10% of a unique ID, unless 10% is less than 20 items in which case the report would pick 20 random ID's. I have been using NewID to generate the 10%, but that really isn't the best as it gives me variable results (IE: more or less than 10%)

Code also includes my attempts to get a total count of the results:

select  UniqueID, TotalCount = Count(*) Over(), SUM(COUNT(UniqueID)) OVER() 
AS total_count 
from table 
where 0.15 >= CAST(CHECKSUM(NEWID(), UniqueID) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
group by UniqueID




Aucun commentaire:

Enregistrer un commentaire