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

No comments:

Post a Comment

Getting Error Description and Error Column Name in SSIS 2016...

If you check the Error Output Path of a SSIS Data Flow component, it only contains the ErrorCode and the ErrorColumn (actually this the I...