How to Round Numbers to Two Decimal Places in SQL

1. Introduction

Rounding numbers to two decimal places is a common requirement in SQL, especially when dealing with financial, scientific, and other precise data. Furthermore, SQL provides various functions to round numbers, and the syntax can vary slightly depending on the SQL dialect being used.

In this tutorial, we’ll cover how to round numbers to two decimal places in SQL. First, we’ll discuss the ROUND function. After that, we’ll cover TRUNCATE, followed by FORMAT. Lastly, we’ll explore CAST and CONVERT.

Notably, we execute the queries MySQL and MS SQL server database management system using tables in the Baeldung University database schema.

2. Sample Column

For demonstration purposes, we create a sample column called scores in the Exam table:

ALTER TABLE exam
ADD COLUMN scores DECIMAL(10, 3);

Moreover, we input some dummy data to check the query results:

UPDATE exam SET scores = 87.456 WHERE id = 1;
UPDATE exams SET scores = 92.678 WHERE id = 2;
UPDATE exams SET scores = 75.213 WHERE id = 3;
...
;

With this setup, we can successfully execute the queries discussed that follow below.

3. Using the ROUND Function

In SQL, we use the ROUND function to round numbers to the required number of decimal places.

In addition, the syntax of this function involves specifying the column name followed by the number of decimal places to round to:

SELECT ROUND(column_name, 2) AS rounded_value FROM table_name;

For example, to round the scores column in the Exam table in MySQL, we use ROUND:

SELECT ROUND(scores, 2) AS rounded_scores FROM exam;
rounded_scores
84.46
92.68
75.21
...

Notably, the ROUND function is a standard SQL function that works across various SQL engines and their versions.

4. Using the TRUNCATE Function

Alternatively, we can use the TRUNCATE function to shorten a number to a specified number of decimal places without rounding. In other words, TRUNCATE cuts off digits beyond the desired precision. Thus, the function is handy when discarding extra precision without altering the remaining digits.

Therefore, we can use TRUNCATE to round decimals places in MYSQL:

SELECT TRUNCATE(scores, 2) AS rounded_scores FROM exam;
rounded_scores
84.45
92.67
75.21
...

In MSSQL, since the TRUNCATE function isn’t available, we can achieve similar functionality using CAST or CONVERT with integer arithmetic.

5. Using the FORMAT Function

Another approach to round off numbers to two decimal places is to use the FORMAT function. In particular, the FORMAT function is used to convert numbers to a string representation with a specified format.

Along the way, the FORMAT function also rounds the numbers where necessary.

For instance, to format the column score from the exam table, we pass the column name and the desired number of decimal places:

SELECT FORMAT(scores, 2) FROM exam AS formatted_scores;
formatted_scores
84.46
92.68
75.21
...

However, in MSSQL, we use N2 instead of 2 to represent two decimal places:

SELECT FORMAT(scores, 'N2') FROM exam AS formatted_scores;
formatted_scores
84.46
92.68
75.21
...

Hence, it yields a similar result for the scores column in the Exam table.

6. Using CAST or CONVERT

One more approach to rounding numbers in SQL is casting or converting.

Specifically, we can use these options to change the data type of a number to a string with a specified format or to a numeric type with fixed precision. Furthermore, this approach often includes rounding as part of the conversion process. However, this method only works with MSSQL.

For example, to round off the scores column in the Exam table, we convert the scores column to the decimal type with a precision of two digits after the decimal point:

SELECT CAST(scores AS DECIMAL(10, 2)) AS rounded_scores FROM exam;
rounded_scores
84.46
92.68
75.21
...

In addition, we can use CONVERT the scores column using DECIMAL:

SELECT CONVERT(DECIMAL(10, 2), scores) AS rounded_Scores FROM exam;
rounded_scores
84.48
92.68
75.21
...

This way, we leverage the CAST and CONVERT functions to change the data type of a number, including rounding to a specified number of decimal places.

7. Conclusion

In this article, we learned ways to round numbers in SQL. Initially, we discussed the ROUND function. Subsequently, we explored the TRUNCATE and FORMAT approaches to obtain similar results. Lastly, we covered CAST and CONVERT for the same purpose.