NTILE for distribution in SQL

Introduction

NTILE helps split data into equal parts or groups. It makes it easy to see how data is spread out.

You want to divide sales data into 4 equal groups to find top sellers.

You need to split students' scores into 3 groups: low, medium, high.

You want to create 5 groups of customers based on purchase amounts.

You want to analyze data by dividing it into equal parts for comparison.

Syntax

SQL

NTILE(number_of_groups) OVER (ORDER BY column_name)

number_of_groups is how many parts you want to split your data into.

The ORDER BY inside OVER() decides how rows are sorted before splitting.

Examples

This splits students into 4 groups based on their scores, highest score gets quartile 1.

SQL

SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM students;

This divides products into 3 groups by price from lowest to highest.

SQL

SELECT product, price, NTILE(3) OVER (ORDER BY price) AS price_group FROM products;

Sample Program

This example creates a sales table, inserts 5 rows, then splits them into 2 groups by amount.

SQL

CREATE TABLE sales (id INT, amount INT);
INSERT INTO sales VALUES (1, 100), (2, 200), (3, 300), (4, 400), (5, 500);
SELECT id, amount, NTILE(2) OVER (ORDER BY amount) AS half FROM sales;

Important Notes

If the number of rows is not divisible by the number of groups, some groups will have one more row.

NTILE assigns group numbers starting at 1 up to the number of groups.

Summary

NTILE splits data into equal groups based on order.

Use it to compare parts of your data easily.

Groups are numbered starting at 1.