Calculating What Percentage Value Of Top Five Records From A Single Column

Question

Hello! I have a column of numbers and want to calculate in SSMS what percentage of the whole the top five make up. In Excel the answer I got back was 89.99619%

I then want to concat that value to a sentence, so my final output would be "The top five records count for 89.99619% of the total", or something like that.

I cannot figure out how to get the top five values without using order by, which I know is not allowed in a subquery. Thank you all for looking at this matter for me.

Here are my values...

1502359.92
974102.52
144425.43
138021.46
101418.03 <-- this is the last of the top five

79395.06
78987.63
66347.38
46811.04
24318.51
22089.14

Answer 1

Select *, concat('The top five records count for ',format(percentOfTop5,'P4'),' of the total ',total)  
from (  
select num,  
row_number() Over( order by num desc) rn,  
 Cast(SUM(num) OVER(ORDER BY num desc)*1.0 /SUM(num) Over ()  as decimal(18,6)) AS percentOfTop5  
,SUM(num) Over () total  
from testNum  
) t   
 where rn=5  
   

Answer 2

Outstanding! Thank you so much!

Answer 3

You can actually use ORDER BY in subqueries, if you combine it with top. Here is a different solution, not necessarily better than Jingyang's.

   CREATE TABLE #numbers(n decimal(10,2))  
   INSERT #numbers (n)VALUES  
   (1502359.92),  
   (974102.52 ),  
   (144425.43 ),  
   (138021.46 ),  
   (101418.03 ),  
   (79395.06  ),  
   (78987.63  ),  
   (66347.38  ),  
   (46811.04  ),  
   (24318.51  ),  
   (22089.14  )  
   go  
   ; WITH top5 AS (  
      SELECT TOP 5 n FROM #numbers ORDER BY n DESC  
   ), calcperc AS (  
      SELECT 1E2*(SELECT SUM(n) FROM top5) /  
                 (SELECT SUM(n) FROM #numbers) AS percentage  
   )  
   SELECT concat('The top 5 values account for, ', str(percentage, 8,6), ' % of the total')  
   FROM   calcperc                                                     
   go  
   DROP TABLE #numbers