Earn income with your data and sql skills
We're building the largest freelancing marketplace for people like you.

SQL NTILE Function

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)
``````
Result:  12 records
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.

NTILE with UNEQUAL DISTRIBUTION

Problem: List all products with prices between \$1 and \$10. Group the list into four equal parts (i.e. quartiles).
``````SELECT ProductName, UnitPrice,
NTILE(4) OVER(ORDER BY UnitPrice) AS Quartile
FROM Product
WHERE UnitPrice BETWEEN 1 AND 10
ORDER BY UnitPrice
``````
Result:  15 records
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.

NTILE with OTHER RANKING FUNCTIONS

Problem: List products with prices between \$8 and \$12. Include some common ranking functions based on price.
``````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
``````
Result:  10 records
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

You may also like

Last updated on Dec 21, 2023

Earn income with your data and sql skills