title: Bucketing percentiles
body: <p>Something I have an ongoing demand for is putting personnel into "buckets" based on certain performance metrics.  NTILE doesn't do what I want since it splits the population into equal divisions.  Graphically, you can see what I want here:</p>
  
  <p><img src="https://i.stack.imgur.com/OSoZf.png" alt="Percentages"></p>
  
  <p>This essentially comes up with each person's position so that management can categorize them.  Here you can see the "top" 10 percent of qty is provided by 12 or 13 people (where the "cumulative" column reaches 10).  Not pictured is the "bottom" 10 percent, which is populated by over 200 people.  I am currently accomplishing this with Excel, but I would prefer to keep the complexity on the server side.  </p>
  
  <p>Here is how I get to percentage:</p>
  
  <pre><code>SELECT PREmain.*
       , PREtotal.[Qty] [Total Qty]
       , (PREmain.[Qty] / PREtotal.[Qty]) * 100 [Qty Percentage]
  FROM PRE PREmain INNER JOIN
       (SELECT [Week]
              ,[Year]
              ,SUM([Qty]) [Qty]
        FROM PRE
        GROUP BY [Week]
                ,[Year]) PREtotal ON PREmain.[Week] = PREtotal.[Week]
                                 AND PREmain.[Year] = PREtotal.[Year]
  </code></pre>
  
  <p>...where "PRE" is populated by another query.  Can somebody tell me how I can accomplish this?</p>
  
  <p><strong>edit</strong></p>
  
  <p>I am currently stuck with 2008 R2, but the below answers will hopefully provide a catalyst to rationalize a 2012 upgrade.With the suggestions below I searched for "SUM OVER SQL 2008" and found this:</p>
  
  <p><a href="https://stackoverflow.com/questions/7652511/sql-server-2008-using-sum-overorder-by">SQL Server 2008 using SUM() OVER(ORDER BY...)</a></p>
  
  <p>This applies to 2008 and, modified, worked for me.  Here's my query:</p>
  
  <pre><code>,
  
  PRE2 AS (
      SELECT PREmain.*
           , PREtotal.[Qty] [Total Qty]
           , (PREmain.[Qty] / PREtotal.[Qty]) * 100 [Qty Percentage]
           , PREtotal.[Gross Sales] [Total Gross Sales]
           , (PREmain.[Gross Sales] / PREtotal.[Gross Sales]) * 100 [Gross Sales Percentage]
           , PREtotal.[Net Sales] [Net Sales Qty]
           , (PREmain.[Net Sales] / PREtotal.[Net Sales]) * 100 [Net Sales Percentage]
      FROM PRE PREmain INNER JOIN
           (SELECT [Week]
                  ,[Year]
                  ,SUM([Qty]) [Qty]
                  ,SUM([Gross Sales]) [Gross Sales]
                  ,SUM([Net Sales]) [Net Sales]
            FROM PRE
            GROUP BY [Week]
                    ,[Year]) PREtotal ON PREmain.[Week] = PREtotal.[Week]
                                     AND PREmain.[Year] = PREtotal.[Year])
  
  SELECT a.*, x.s [Cumulative]
  FROM PRE2 a
       CROSS APPLY (   SELECT ISNULL(SUM(v), 0)
                      FROM (  SELECT b.[Qty Percentage]
                              FROM PRE2 b
                              WHERE b.[Qty Percentage] &lt; a.[Qty Percentage]
                                AND a.[Week] = b.[Week]
                               ) x(v)
                  ) x(s)
  ORDER BY a.[Qty Percentage]
  </code></pre>
  
  <p>It took 3 minutes to get 1719 results.  Kind of slow, but it's what I've got to work with until I can upgrade to 2012.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>It's way faster with TABLE variables.  Runs in about 4 seconds.</p>