The PostgreSQLROUND() function rounds a numeric value to its nearestinteger or a number with the number of decimal places.

Syntax

The following illustrates the syntax of theROUND() function:

ROUND (source [ ,n ] )

Arguments

TheROUND() function accepts 2 arguments:

1) source

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

2) n

Then 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

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

If you use both arguments, theROUND() function returns a numeric value.

Examples

1) Basic ROUND() function example

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

SELECT    ROUND( 10.4 );

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

10

The following example rounds 10.5:

SELECT    ROUND( 10.5 );

Output:

11

2) Round to 2 decimal places examples

The following example uses theROUND() function to round a number to the one with 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.

3) Rounding data from table examples

We will use the following payment andcustomer tables in thesample database for the demonstration.

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

SELECT    first_name,    last_name,    ROUND( AVG( amount), 2 ) avg_rentalFROM    paymentINNER JOIN customer        USING(customer_id)GROUP BY    customer_idORDER BY    avg_rental DESC;

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

Output:

first_name  |  last_name   | avg_rental-------------+--------------+------------ Brittany    | Riley        |       5.62 Kevin       | Schuler      |       5.52 Ruth        | Martinez     |       5.49 Linda       | Williams     |       5.45 Paul        | Trout        |       5.39 Daniel      | Cabral       |       5.30...

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;

Output:

round-------    24(1 row)

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

Summary

• Use the PostgreSQLROUND() function to round a number to its nearest integer or a number of specified decimal places.

Last updated onFebruary 16, 2024

On this page

• Syntax
• Arguments
• Return value
• Examples
• Summary