The NTILE function groups rows into a number of parts of equal size.
The parts (or tiles) are numbered, starting at 1.
Further grouping can be applied to groups (partitions) within the rows.
If the row count is not divisible by the number of parts, rows will be distributed unevenly.
Note: The word NTILE is a general form of tertile, quartile, quintile, etc.
For example, a population split into three equal parts is divided into tertiles.
List the monthly sales for the year 2013.
Partition the list into three equal parts (i.e. tertiles).
SELECT MONTH(OrderDate) AS [Month],
SUM(TotalAmount) AS 'Total Sales',
NTILE(3) OVER(ORDER BY MONTH(OrderDate)) AS Tertile
FROM [Order]
WHERE YEAR(OrderDate) = 2013
GROUP BY MONTH(OrderDate)
| Month | Total Sales | Tertile |
|---|---|---|
| 1 | 66692.80 | 1 |
| 2 | 41207.20 | 1 |
| 3 | 39979.90 | 1 |
| 4 | 55699.39 | 1 |
| 5 | 56823.70 | 2 |
| 6 | 39088.00 | 2 |
| 7 | 55464.93 | 2 |
| 8 | 49981.69 | 2 |
| 9 | 59733.02 | 3 |
| 10 | 70328.50 | 3 |
| 11 | 45913.36 | 3 |
| 12 | 77476.26 | 3 |
Syntax of the NTILE function.
NTILE(number)
OVER( PARTITION BY expression, ...
ORDER BY expression, ... )
number -- a positive number that specifies the number of sections the rows will be divided into.
PARTITION BY expression -- optional. Organizes rows into different groups where the NTILE will be applied. If not specified, entire result set represents a single group.
ORDER BY expression -- set the sort order of the result set.
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SELECT ProductName, UnitPrice,
NTILE(4) OVER(ORDER BY UnitPrice) AS Quartile
FROM Product
WHERE UnitPrice BETWEEN 1 AND 10
ORDER BY UnitPrice
| ProductName | UnitPrice | Quartile |
|---|---|---|
| Geitost | 2.50 | 1 |
| Guaraná Fantástica | 4.50 | 1 |
| Konbu | 6.00 | 1 |
| Filo Mix | 6.00 | 1 |
| Tourtière | 7.45 | 2 |
| Rhönbräu Klosterbier | 7.75 | 2 |
| Tunnbröd | 9.00 | 2 |
| Teatime Chocolate Biscuits | 9.20 | 2 |
| Rogede sild | 9.50 | 3 |
| Zaanse koeken | 9.50 | 3 |
| Jack's New England Clam Chowder | 9.65 | 3 |
| Stroopwafels | 9.75 | 3 |
| Longlife Tofu | 10.00 | 4 |
| Sir Rodney's Scones | 10.00 | 4 |
| Aniseed Syrup | 10.00 | 4 |
Note: Since the row count is not divisible by the number of groups, the distribution is unequal. The last group does not have the same number of rows as the first three groups.
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SELECT ProductName, UnitPrice,
ROW_NUMBER() OVER(ORDER BY UnitPrice) AS RowNumber,
RANK() OVER(ORDER BY UnitPrice) AS Rank,
DENSE_RANK() OVER(ORDER BY UnitPrice) AS DenseRank,
NTILE(4) OVER(ORDER BY UnitPrice) AS Quartile
FROM Product
WHERE UnitPrice BETWEEN 8 AND 12
ORDER BY UnitPrice
| ProductName | UnitPrice | RowNumber | Rank | DenseRank | Quartile |
|---|---|---|---|---|---|
| Tunnbröd | 9.00 | 1 | 1 | 1 | 1 |
| Teatime Chocolate Biscuits | 9.20 | 2 | 2 | 2 | 1 |
| Rogede sild | 9.50 | 3 | 3 | 3 | 1 |
| Zaanse koeken | 9.50 | 4 | 3 | 3 | 2 |
| Jack's New England Clam Chowder | 9.65 | 5 | 5 | 4 | 2 |
| Stroopwafels | 9.75 | 6 | 6 | 5 | 2 |
| Aniseed Syrup | 10.00 | 7 | 7 | 6 | 3 |
| Sir Rodney's Scones | 10.00 | 8 | 7 | 6 | 3 |
| Longlife Tofu | 10.00 | 9 | 7 | 6 | 4 |
| Spegesild | 12.00 | 10 | 10 | 7 | 4 |
