PostgreSQL Tutorial: ROUND Function

August 7, 2023

Summary: The PostgreSQL ROUND() function rounds a numeric value to its nearest integer or a number with the number of decimal places.

Table of Contents

Syntax

The following illustrates the syntax of the ROUND() function:

ROUND (source [ , n ] )

Arguments

The ROUND() function accepts 2 arguments:

\1) source

The source argument is a number or a numeric expression that is to be rounded.

\2) n

The n argument is an integer that determines the number of decimal places after rounding.

The n argument is optional. If you omit the n argument, its default value is 0.

Return Value

The ROUND() function returns a result whose type is the same as the input if you omit the second argument.

In case if you use both arguments, the ROUND() function returns a numeric value.

Examples

A) Round to an integer example

The following example shows how to round a decimal using the ROUND() function:

SELECT
    ROUND( 10.4 );

Because the nearest integer of 10.4 is 10, the function returns 10 as expected:

The following example rounds 10.5:

SELECT
    ROUND( 10.5 );

The result is:

B) Round to 2 decimal places examples

The following example illustrates how to round to 2 decimal places:

SELECT
    ROUND( 10.812, 2 );

Result

10.81

And another example of rounding a decimal to 2 decimal places:

SELECT
    ROUND( 10.817, 2 );

Result

10.82

You can change the second argument to round a number to specific decimal places.

C) Rounding data from table examples

We will use the following payment and customer tables in the sample database for the demonstration.

customer and payment tables

The following statement retrieves the average rental fee that each customer has paid.

SELECT
    first_name,
    last_name,
    ROUND( AVG( amount ), 2 ) avg_rental
FROM
    payment
INNER JOIN customer
        USING(customer_id)
GROUP BY
    customer_id
ORDER BY
    avg_rental DESC;

In this statement, we use the ROUND() function to round average rental fee to 2 decimal places.

The following picture illustrates the result:

PostgreSQL ROUND function example

The following statement calculates the average number of rentals per customer.

WITH rental(customer_id,rent) AS
(
    SELECT
        customer_id,
        COUNT( rental_id )
    FROM
        payment
    GROUP BY
        customer_id
)
SELECT 
    ROUND(AVG(rent)) 
FROM 
    rental;

In this example, we used the ROUND() function to round the result to an integer.

In this tutorial, you have learned how to use the PostgreSQL ROUND() function to round a number to its nearest integer or to a number of a specified decimal places.