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);
OR
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:

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

round_value
3.14

Example 2

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

An example is illustrated below:

SELECT
    ROUND(3.14159) AS round_value;

The result is provided below:

round_value
3.0

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:

SELECT
    ROUND(3.14159, -2) AS round_value;

The previous example should return as follows:

round_value
0.0

Example 4

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

SELECT
    ROUND('NaN', 0) AS round_value;

The function should return a NaN as shown below:

round_value
-------------
NaN
(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 :

Conclusion

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.

from https://ift.tt/SyZ8Mqa