# How to Use CUME_DIST Function in PostgreSQL

> In PostgreSQL, the CUME_DIST() function retrieves the cumulative distribution of a value for the given set of values.

While working with databases, users may need to make a report that shows the very best or worst percentage of values from a set of data. For example, a user may want to calculate the top 1% of products with the highest or lowest revenue. For this purpose, Postgres offers a very convenient function named **CUME_DIST()** that can help us deal with such scenarios.

This write demonstrates how to use CUME_DIST Function in Postgres using the following outlines:

\- Applying CUME_DIST() Function Over a Particular Table

\- Applying CUME_DIST() Function Over a Table Partitions

 **How to Use CUME_DIST Function in PostgreSQL?**

 **CUME_DIST()** is one of the popularly used **Window** functions in Postgres that retrieves the cumulative distribution of a value for the given set of values.

Check the following syntax that will help you understand how to use **CUME_DIST()** function in Postgres:
    
    
    CUME_DIST()
    OVER (
    [PARTITION BY partition_col_list]
    [ORDER BY order_col_list]
    )

In the above syntax:

\- " **OVER** " is a keyword that shows the starting point of the window function's operation. It tells the stated window function which rows to consider for its calculations.

\- “ **[PARTITION BY partition_col_list]** ” is an optional clause that splits the result set into partitions based on columns specified in “ **partition_col_list** ”.

\- Once the result set is divided into partitions, then the specified window function will be applied separately to each partition. However, if you omit this clause, the whole table/result set will be considered as a single partition.

\- The **ORDER BY** is an optional clause that can be used within the CUME_DIST() function to arrange the rows within each partition.

\- The stated function retrieves a double precision value in the following range: “ **0 < CUME_DIST() <= 1**”

Let’s set up a sample table and practically implement the CUME_DIST() function on that table.

 **Sample Table**

Let’s begin with creating a sample table named “transaction_details” that keeps the revenue details:
    
    
    CREATE TABLE transaction_details(
    person_name TEXT NOT NULL,
    year SMALLINT CHECK (year > 0),
    transaction_amount DECIMAL(10,2) CHECK (transaction_amount >= 0),
    PRIMARY KEY (person_name, year)
    );

Once the desired table is created, insert some new records into it using the following query:
    
    
    INSERT INTO transaction_details(person_name, year, transaction_amount)
    VALUES ('Alexa', 2021, 150000),
    ('Joseph', 2020, 95000),
    ('Daniel', 2021, 135000),
    ('Anna', 2022, 125000),
    ('Stephan', 2023, 180000),
    ('Alex', 2021, 250000),
    ('Joe', 2021, 90000);

Let’s fetch the transaction_details table to confirm the inserted data:
    
    
    SELECT * FROM transaction_details;

 **Example 1: Applying CUME_DIST() Function Over a Particular Table**

In this example, we will apply the **CUME_DIST()** function on the “transaction_details” table:
    
    
    SELECT person_name, year, transaction_amount,
    CUME_DIST() OVER (
    ORDER BY transaction_amount
    ) 
    FROM transaction_details
    WHERE year = 2021

The output demonstrates that in the year 2021, the transaction amount of 75% of people was less than or equal to “150000”.

 **Example 2: Applying CUME_DIST() Function Over a Table Partition**

In this example, we will apply the CUME_DIST() function on the table’s partition instead of the entire result set:
    
    
    SELECT person_name, year, transaction_amount,
    CUME_DIST() OVER (
    PARTITION BY DESC year
    ORDER BY transaction_amount
    ) 
    FROM transaction_details

The output snippet depicts that the given result set has been divided into partitions (with respect to year), and the CUME_DIST() function has been successfully implemented over each partition:

That’s all about the CUME_DIST() function in Postgres.

 **Conclusion**

In PostgreSQL, the **CUME_DIST()** function retrieves the cumulative distribution of a value for the given set of values. The PARTITION BY and ORDER BY clauses can be used with the CUME_DIST() function to split the result set into partitions and to sort the partition/result set, respectively. Moreover, the stated function can be applied over an entire result set or a specific partition depending upon the user's needs. This post has explained how to use the CUME_DIST() function in PostgreSQL.

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