Numbering data into specific number of groups using NTILE function...

Sometimes we may need to cluster data into particular group according to the particular value.

As an example as per the SQL Rank... secenario, we may need to distribute rows to 10 groups depending on the AvgRate value. 

For that we can use NTILE function as we used the Rank function. The difference in the NTILE function is that we need to provide the number of groups we want.

Sample query is as below

   1: SELECT YearKey, SupplierKey, ItemKey, NTILE(5) OVER (PARTITION BY ItemKey, YearKey ORDER BY AvgRate ASC) AS GroupByAverageRate FROM FactPurchases GROUP BY YearKey, SupplierKey, ItemKey