# How to use PostgreSQL NTILE Function

> The NTILE function is used to divide the rows of the table into groups provided by the users in the argument of the function called buckets in PostgreSQL.

PostgreSQL is a database server that allows the user to manage their huge pool of data by storing it in the form of tables and accessing that later. It enables the user to fetch data from the database using the SQL queries in the PostgreSQL database. It also allows the user to compare data inside the table by dividing the rows into multiple groups or partitions.

This guide will explain the process of using the NTILE function in PostgreSQL with the help of examples.

 **PostgreSQL NTILE Function Explained With Example**

The NTILE function is used to distribute the rows of the PostgreSQL table into a specified number of groups as equal in size as the table allows. If the data rows are not divisible by the number of groups then the function divides it into different sizes. Larger groups are placed before the smaller groups and the function divides the rows according to size provided by the user.

 **Syntax**

To use the NTILE function in the PostgreSQL database, use the syntax mentioned below:
    
    
    NTILE(buckets) OVER (
       [ORDER BY sort_expression [ASC | DESC], ...]
       )

The above code block depicts:

\- The code uses the **NTILE** keyword suggesting the name of the function and **buckets** as its argument to set the number of groups for the function.  
\- The **buckets** parameter determines the number of divisions a table will be partitioned into like 2 means that the table will be divided into 2 parts and so on.  
\- The **ORDER BY** clause is used to get the order of the tables using a **column** from the table and it is mandatory for using the **NTILE** function.  
\- The **PARTITION BY** clause is the optional part of the function which can be used to get groups based on the values of the column.

 **Example 1: How to Use NTILE Function in PostgreSQL?**

To use the NTILE function in PostgreSQL, simply create a table and insert data in it and then use the following query to access data from it:
    
    
    SELECT * FROM employee;

The following screenshot displays the data from the employee table in PostgreSQL:

Use the following code to divide the table using the **NTILE** function in the PostgreSQL database:
    
    
     SELECT name, gender, salary,
      NTILE(4) 
      OVER (ORDER BY salary) 
     FROM employee
     WHERE salary >= 5000;

The above code selects columns of the table and uses the **NTILE** function with 4 **buckets** to keep the data of the table in 4 parts. The code uses a **salary** column to order the table and it only divides the employees with **salary** more than **5000** :

The above screenshot displays that there are 6 employees earning 5000 or more and the NTILE function has divided the table into 4 groups. The first two groups have 2 rows and the last 2 only contain 1 row in it as the number of rows is not divisible by 4.

 **Example 2: PARTITION BY Clause in NTILE Function From PostgreSQL**

The following code uses the PARTITION BY clause in the NTILE function to make partitions in the employee table:
    
    
    SELECT name, gender, salary,
      NTILE(4) 
      OVER(PARTITION BY gender
       ORDER BY salary) 
     FROM employee;

The following screenshot displays that the NTILE function divides the table but separately as the PARTITION BY clause has already divided it into 2 parts. The Female has 4 rows so in this case each group contains a single value but Males were 6 in number so it divides the table as it was in the first example:

That’s all about using the NTILE function in the PostgreSQL table with the help of examples.

 **Conclusion**

To use the NTILE function in PostgreSQL, simply create a table and insert data in it to use the NTILE function on it. The NTILE function is used to divide the rows of the table in the form of buckets which will be mentioned in its arguments. Each bucket contains the number of rows from the PostgreSQL table and buckets with greater rows are placed above others. This guide has explained the process of using the NTILE function in the PostgreSQL table with the help of examples.

---
[View this page online](https://www.commandprompt.com/education/how-to-use-postgresql-ntile-function/)