PostgreSQL Tutorial

  • Home
  • Stored Procedures
  • Triggers
  • Views
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
  • Functions
    • Aggregate Functions
    • Date / Time Functions
    • String Functions
    • Math Functions
Home / PostgreSQL Math Functions / PostgreSQL ROUND Function

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:

1
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:

1
2
SELECT
    ROUND( 10.4 );

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

1
10

The following example rounds 10.5:

1
2
SELECT
    ROUND( 10.5 );

The result is:

1
11

B) Round to 2 decimal places examples

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

1
2
SELECT
    ROUND( 10.812, 2 );

Result

1
10.81

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

1
2
SELECT
    ROUND( 10.817, 2 );

Result

1
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.

1
2
3
4
5
6
7
8
9
10
11
12
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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.

Previous Tutorial: PostgreSQL ABS Function
Next Tutorial: PostgreSQL CEIL Function

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete

Managing Table Structure

  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

PostgreSQL Views

  • Managing PostgreSQL Views
  • Creating Updatable Views
  • PostgreSQL Materialized Views
  • The WITH CHECK OPTION Views
  • PostgreSQL Recursive View

PostgreSQL Triggers

  • Introduction to Trigger
  • Creating A Trigger
  • Managing PostgreSQL Triggers

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • How To Delete Duplicate Rows in PostgreSQL
  • PostgreSQL TO_CHAR Function
  • PostgreSQL TO_NUMBER Function
  • PostgreSQL TO_TIMESTAMP Function
  • PostgreSQL CEIL Function
  • PostgreSQL MOD Function
  • PostgreSQL FLOOR Function
  • PostgreSQL ABS Function
  • PostgreSQL TRUNC Function
  • PostgreSQL ROUND Function

More Tutorials

  • PostgreSQL Cheat Sheet
  • PostgreSQL Administration
  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2017 by PostgreSQL Tutorial Website. All Rights Reserved.