How to Roundup in SQL? [100% Working]

What is Roundup in SQL?

Rounding is the process of simplifying a number to a shorter or approximate value, making it closer to a specified level of precision. This simplification can be to the nearest whole number, decimal place, or any other specified value. There are several methods of rounding, such as rounding up, rounding down, and rounding to the nearest value.

For example:

• Rounding 3.14159 to two decimal places yields 3.14.
• Rounding 3.6 to the nearest whole number yields 4.
• Rounding 3.4 to the nearest whole number yields 3.

The ROUND() function in SQL Server can be used to round numerical values. In addition, SQL Server has other rounding methods like ROUNDUP() and CEILING() that carry out various rounding processes. Here is how to utilise these features.

 

Different Rounding functions in SQL

• ROUND() adjusts a number to a specified number of decimal places, rounding to the nearest value based on precision.
• CEILING() rounds a number up to its nearest whole number, regardless of its fractional part.
• FLOOR() rounds a number down to its nearest whole number, irrespective of its fractional part.

 

ROUND() Function in SQL

The ROUND() function is used to round a number to a specified number of decimal places. It follows the standard rounding rules. If the decimal portion is 5 or greater, the number is rounded up. The syntax is as follows:

ROUND(numeric_expression | Column_name, length [, function]);

Here,

• numeric_expression: The number or column you want to round.
• length: The number of decimal places to which you want to round the value.
• function (optional): Specifies the type of rounding operation. If not provided, the default is to round to the nearest value.

 

Examples of SQL ROUND() function

Consider online book selling system to perform practical examples on SQL ROUND() function

Book Table

Customer Table 

Order Table

 

Example 1: Write SQL query to round up number 123.651 to 1 decimal digit using SQL ROUND() function

SELECT  ROUND(123.651, 1) AS 'RoundUp'

• In the above query, SQL ROUND() function is used to round calculates the rounded value of the number 123.651 to one decimal place.
• The function takes two arguments: 123.651 is the number we want to round, and 1 specifies that we want to round it to one decimal place.

OUTPUT:

Example 2: Write SQL query to roundup book order amount to zero decimal place

select orderid,customerid,bookid,quntity,ROUND(total_amount,0)as'Roundup Total'
from tblorder;

• In the above query , SQL select statement is used to retrieves information about orders from the tblorder table, and in addition to the original columns, it includes a rounded version of the total_amount column for each order in the result set.
• The ROUND() function is used to round the total_amount to zero decimal places, effectively rounding it to the nearest whole number.

OUTPUT:

 

CEILING() function in SQL

In SQL, the CEILING() function is used to round a number up to the nearest integer or a chosen decimal place. It only requires a single argument and outputs the smallest integer bigger than or equal to the input.

The syntax for the CEILING() function varies slightly depending on the database management system (DBMS) you are using.

CEILING(value| Column_name);

 

Examples of SQL CEILING() function

Example 3: Write SQL query to roundup number 123.650 using SQL CEILING() function

SELECT  CEILING(123.650) AS 'RoundUp'

• In the above query , SQL CEILING() function is used to round up the value 123.650 to the nearest integer. The result of the rounding operation is then returned as a column labeled 'RoundUp'.
• The argument passed to the CEILING() function is 123.650, which has a decimal part, the function will round it up to the next higher integer. Therefore, the result of this query will be a single row with the column 'RoundUp' having the value 124.

OUTPUT:

Example 4: Write SQL query to roundup book order billing amount

Select orderid,customerid,bookid,quntity,CEILING(total_amount)from tblorder;

• In the above query, SQL select statement is used to retrieves data from the table tblorder and calculates the ceiling value of the total_amount column.
• SQL CEILING() function rounds up the values of total_amount to the nearest integer.

OUTPUT:

 

FLOOR() function in SQL

In SQL Server, the FLOOR() function is used to round a numerical value to the nearest integer or a certain decimal place. It gives back the biggest number that is either smaller than or equal to the argument.

FLOOR(value| Column_name)

 

Examples of SQL FLOOR() function

Example 5: Write SQL query to roundup value 123.650 using SQL FLOOR() function

SELECT  FLOOR(123.650) AS 'FLOOR RoundUp ';

• In the above query , SQL select statement is used to to round down the value 123.650 to the nearest integer. The result of the rounding operation is then returned as a column labeled 'FLOOR RoundUp'.
• The argument passed to the FLOOR() function is 123.650, which has a decimal part, the function will round it down to the next lower integer. Therefore, the result of this query will be a single row with the column 'FLOOR RoundUp' having the value 123.

OUTPUT:

Example 6: Write SQL query to roundup book order total amount

Select orderid,customerid,bookid,quntity,FLOOR(total_amount)as'FLOOR Total Amount' 
from tblorder;

• In the above query, SQL select statement is used to retrieves data from the table tblorder and calculates the floor value of the total_amount column.
• SQL FLOOR() function Applies to the total_amount column. This function rounds down the values of total_amount to the nearest integer.

OUTPUT:

 

Difference between SQL ROUND() Vs CEILING() Vs FLOOR() functions

Here's a breakdown of each function and the differences between them:

ROUND(): The ROUND() function is used to round a numeric value to a specified decimal place. You can use this function to round a number up or down, depending on the fractional part of the number being rounded. If the fractional part of the number is less than 0.5, the number is rounded down; otherwise, it's rounded up. You can also specify the number of decimal places to round to.

For example, ROUND(3.14159, 2) would return 3.14.

CEILING(): The CEILING() function is used to round a numeric value up to the nearest integer or a specified decimal place. It always rounds the number up, regardless of the fractional part of the number being rounded.

For example, CEILING(3.14159) would return 4, because 3.14159 is closer to 4 than to 3.

FLOOR(): The FLOOR() function is used to round a numeric value down to the nearest integer or a specified decimal place. It always rounds the number down, regardless of the fractional part of the number being rounded.

For example, FLOOR(3.14159) would return 3, because 3.14159 is closer to 3 than to 4.

 

Summary

In this article on RoundUp in SQL, We have discuss what is RoundUp , functions to be used to perform RoundUp in SQL, explain three functions SQL ROUND(),SQL CEILING() and SQL FLOOR() with practical examples and also covered the difference between SQL ROUND(), SQL CEILING() and SQL FLOOR() function.

 

References

SQL NUMERIC FUNCTIONS

 

Read More

SQL Round