PostgreSQL ROUND() Function

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

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.

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

Examples

1) Basic ROUND() function 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:

10

The following example rounds 10.5:

SELECT
 ROUND( 10.5 );

Output:

11

2) Round to 2 decimal places examples

The following example uses the ROUND() 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 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 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 the ROUND() function to round the result to an integer.

Summary

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