SQL Standard

SQL Round to 2 Decimal Places

Mathematical operations allow us to clean, filter, analyze, and build meaningful datasets when working with databases.

This short article will discuss how to round off a numeric value to a specific decimal place.

SQL Round() Function

The round() function in SQL allows you to round off a numeric value to a specific precision point. Although defined in the Standard SQL, this function is widely adopted by significant database engines, such as MySQL, PostgreSQL, SQL Server, and BigQuery.

The function syntax is shown below:

round(i NUMERIC, d INT);
ROUND(numeric_expr [, decimal_places])

The round function takes two arguments. The first is the numeric value to round off. The second is the number of decimal places to round off

If the value of d is not specified during the function call, the function will automatically round the specified numeric value to the nearest integer.

The function will return the following type based on the input type:

  1. Int64(input) -> float64(output)
  2. Numeric(input) -> numeric(output)
  3. Bignumeric(input) -> bignumeric(output)
  4. Float64(input) -> float64(output).

Apart from an integer, all the other types will return their input type as the output.

Example 1

The following example shows how to use the round function in a simple setting:

    ROUND(3.14159, 2) AS round_value;

The previous example query will round off the specified value to 2 decimal places. The resulting value is shown below:


Example 2

If the precision point is not specified, the round function will round off to the nearest integer.

An example is illustrated below:

    ROUND(3.14159) AS round_value;

The result is provided below:


Example 3

If you specify a negative value for the precision point, the round function will attempt to round the values on the left side of the decimal point.

An example is shown below:

    ROUND(3.14159, -2) AS round_value;

The previous example should return as follows:


Example 4

What if you specify a NaN value? Consider the example below:

    ROUND('NaN', 0) AS round_value;

The function should return a NaN as shown below:

(1 ROW)

Example 5

To use the round function in a table column, we can run a query as shown below:

select payment_id, round(amount, 1) as appx_amount from payment;

The previous query should round off the values of the amount column to one decimal place. An example output is provided below :


This article discusses how to round off a numeric value to any number of decimal places using the round function. We hope you found this article helpful. Check the other Linux Hint articles for more tips and tutorials.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list