how to use NTILE function in oracle database query

The NTILE function in Oracle SQL / PLSQL divides an ordered set of data or a record into number of buckets indicated by expression and assigns the appropriate bucket number to each record. The buckets are numbered 1 upto expression. The expression value must resolve to a positive constant for each partition.

Syntax for the NTILE Function in Oracle SQL / PLSQL is:
SELECT column(s)
,NTILE (expression) OVER ([partition by column(s)] ORDER BY column(s))
FROM table_name(s);

Example 1:

Using NTILE function to without PARTITION BY:

Suppose we have a table named ‘employee’ as shown below:

Employee_Id	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Sales	10

If we write our query for NTILE without PARTITION BY as:

SELECT employee_name
       ,salary
       ,department
       ,NTILE(4) OVER (ORDER BY salary) AS Quartile
FROM employee;

We will get the following output:

Employee_Name	Salary	Department	Quartile
Emp A	10000	Sales	1
Emp H	12000	Sales	1
Emp G	12000	Sales	2
Emp B	20000	IT	2
Emp F	20000	Sales	3
Emp C	28000	IT	3
Emp D	30000	Support	4
Emp E	32000	Sales	4

Example 2:

Using NTILE function to with PARTITION BY:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10
106	Emp F	40000	Sales	10
107	Emp G	12000	Sales	10
108	Emp H	12000	Sales	10

If we write our query for NTILE with PARTITION BY as:

SELECT employee_name
       ,salary
       ,department
       ,NTILE(4) OVER (PARTITION BY department ORDER BY salary) AS Quartile
FROM employee;

We will get the following output:

Employee_Name	Salary	Department	Quartile
Emp B	20000	IT	1
Emp C	28000	IT	2
Emp A	10000	Sales	1
Emp H	12000	Sales	1
Emp G	12000	Sales	2
Emp F	20000	Sales	3
Emp E	32000	Sales	4
Emp D	30000	Support	1