SQL ROUND() Function

SQL ROUND() Function


The SQL ROUND() function is used to round a numeric field (floating point value) to the whole number (integer value without fractional) of decimals specified.

The SQL ROUND() function is converting a floating point value to nearest integer value.

Here, round .1 through .4 DOWN to the next lower integer, and .5 through .9 UP to the next higher integer.

Ex: 5.6 will round to 6 and 3.3 will round to 3

The SQL ROUND() function is supports or work with only numeric based columns.

It can be used in any valid SQL SELECT statement as well in SQL where clause.



Sql round function using round to 2 decimals places, truncate decimal, number functions in sql, ceiling and floor, round to nearest whole integer number.

SQL ROUND() Syntax

The basic syntax is used to round a floating value from a numeric value or expression.


SELECT ROUND(input_number , decimals);

The below syntax is used to round a floating value for a specific table column or field from a specified table.


SELECT ROUND(column_name1 , decimals) FROM table_name1;

Note: The first parameter is used to round the number depends on the input value. The second parameter is used to number of decimal places rounded to and also it is optional parameter. This value must be a positive or negative integer. If this second parameter is omitted, the ROUND() function will round the input number to "0" decimal places.


SQL ROUND() Example - Using Expression Or Formula

The following SQL statement rounds the given input value:


SELECT ROUND(4.1);

The result of above query is:

Expr
4

SQL ROUND() Function More Example

Input Value Result
Round(5.3) 5
Round(6.8) 7
Round(3.5) 4
Round(123.456, 2) 123.460
Round(123.456, 1) 123.500

Sample Database Table - BookInfo

BookId BookName AuthorName BookPrice DomainName
101 Sql Complete Reference Suresh Babu 250.50 Database
102 Sql Commands Haris Karthik 120.30 Database
103 Pl Sql Quick Programming Siva Kumar 150 Programming
104 Sql Query Injection Bala Murugan 199.99 Security
105 The Power Of Pl Sql Suresh Babu 220.80 Programming

SQL ROUND() Example - With Table Column

The following SQL statement selects the bookname and rounds the bookprice in the "BookInfo" table:


SELECT BookName, 
ROUND(BookPrice) As 'Round Price' 
FROM BookInfo;

The result of above query is:

BookName Round Price
Sql Complete Reference 250
Sql Commands 120
Pl Sql Quick Programming 150
Sql Query Injection 200
The Power Of Pl Sql 221


Sql server round using numeric functions, round function in sql server, Round Number to Specific Precision, avg 2 decimal, remove decimal values, hour.

SQL Round Date Day

ROUND() function returns a date rounded to a specific unit.

Syntax:

The syntax for the ROUND function in Oracle PL/SQL is:

ROUND( date [, format] )

Parameters:

Parameters Description
date The date to round.
format Optional. The unit to apply for rounding.If omitted, the ROUND function will round to the nearest day.

format can be can one of the following values:

Unit Valid format parameters Rounding Rule
Year SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Rounds up on July 1st
ISO Year IYYY, IY, I N/A
Quarter Q Rounds up on the 16th day of the second month of the quarter
Month Month, MON, MM, RM Rounds up on the 16th day of the month
Week WW Same day of the week as the first day of the year
IW Same day of the week as the first day of the ISO year
W Same day of the week as the first day of the month
Day DDD, DD, J N/A
Start day DAY, DY, D N/A
Hour HH, HH12, HH24 N/A
Minute MI N/A

Returns: The ROUND function returns a date value.

Example 1: Here we use ROUND() function:

select ROUND(TO_DATE ('22-AUG-20'),'YEAR') from dual;

select ROUND(TO_DATE ('22-AUG-20'),'Q') from dual;

select ROUND(TO_DATE ('22-AUG-20'),'MONTH') from dual;

select ROUND(TO_DATE ('22-AUG-20'),'DDD') from dual;

select ROUND(TO_DATE ('22-AUG-20'),'DAY') from dual;

Example 2: Using the NEXT_DAY, LAST_DAY, ROUND, and TRUNC Functions.

select sysdate
,      next_day(sysdate,'SAT') as next_sat
,      last_day(sysdate)       as last_day
,      round(sysdate,'YY')     as round_yy
,      trunc(sysdate,'CC')     as trunc_cc
from   dual;

Round Number to Specific Precision

The ROUND function is a mathematical function that rounds a number to a specified length or precision. The syntax of the ROUND function is shown below.

Example:

ROUND (numeric_expression, precision); 

ROUND function accepts two arguments.

  • numeric_expression is a formula that evaluates to a number.
  • Precision can only take positive or negative integers.

Example 1: The following statement rounds a number to one decimal place.

SELECT ROUND(100.19,1);

Output:

round
100.2

Example 2: Because the precision is 1, the ROUND function rounds a number up to the nearest decimal. See the following example.

SELECT ROUND(100.14,1);

Output:

round
100.1

Example 3: If the precision is a negative integer, the ROUND function will round numbers on the left side of the decimal point, for example:

SELECT ROUND(109.14,-1);

Output:

round
110

In the above statement, the ROUND function rounded up the last digit of the number on the left of the decimal point. In addition, the numbers on the right side of the decimal point went to zero.

Example 4: Some database systems such as Microsoft SQL Sever, IBM DB2, Sybase ASE display the zero (.00) after the decimal point of the number while the other e.g., Oracle database, PostgreSQL, MySQL do not.

Note that Oracle, PostgreSQL, and MySQL has a version of the ROUND function that accepts a single argument. If you pass a single argument, the ROUND function rounds the number up the nearest integer. For example, the following statement returns 110, which is the nearest integer.

SELECT ROUND(109.59);

Output:

round
110

Example 5: See the following employees and departments tables in the sample database.

emp_dept_tables

The following statement uses the ROUND function to round the average salary of employees in each department to the nearest integers.

SELECT department_name,
    ROUND(AVG(salary), 0) average_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY e.department_id
ORDER BY department_name;

SQL Round Avg 2 Decimal

Example 1: Round avg to decimal values:

We can use ROUND with AVG to get a nicer appearance for our results:

SELECT
  ROUND(AVG(price), 2)
FROM project;

The above query will round the average price to two decimal places. Of course, you can also use AVG without the second argument: ROUND(AVG(price)) to get rounding to integer values.

Example 2: The following example calculates the average standard costs of all products:

SELECT
    ROUND(AVG( standard_cost ), 2) avg_std_cost
FROM
    products;

Notice that we used the ROUND() function to return the average standard cost rounded to 2 decimal places.

Example 3: You can also use multiple AVG() functions in the same query. For example, the following statement calculates the averages of standard costs and list prices:

SELECT
    ROUND(AVG( standard_cost ),2) avg_std_cost,
    ROUND(AVG( list_price ), 2) avg_list_price
FROM
    products;

SQL Round Down Nearest Integer

In MySQL, the FLOOR() characteristic lets in you to spherical more than a few right all the way down to the closest integer. More specifically, it returns the most important integer now no longer large than its argument.

Syntax:

FLOOR(X) 

For exact-fee numeric arguments, the go back fee has an exact-fee numeric type. For string or floating-factor arguments, the go back fee has a floating-factor type.

Example 1 – Positive Values

Here’s an example of providing a positive value as the argument.

SELECT FLOOR(1.87) Result;

Result:

1

In this case, 1 is the largest integer not larger than 1.87.

working with a larger value:

SELECT FLOOR(200.87) Result;

Result:

200

Example 2 – Negative Values

Here’s an example using a negative value.

SELECT FLOOR(-1.87) Result;

Result:

-2

In this case, -2 is the largest integer not larger than -1.87.

Example 3: To round down to nearest integer, use FLOOR() function from MySQL.

Syntax:

SELECT FLOOR(yourColumnName) from yourTableName;

Example 1:

Let us first create a table:

mysql> create table FloorDemo
   -> (
   -> Price float
   -> );
Query OK, 0 rows affected (0.57 sec)

Insert records to column Price. The query to insert records is as follows:

mysql> insert into FloorDemo values(5.75);
Query OK, 1 row affected (0.21 sec)
mysql> insert into FloorDemo values(5.23);
Query OK, 1 row affected (0.31 sec)
mysql> insert into FloorDemo values(5.50);
Query OK, 1 row affected (0.12 sec)

Display the records present in the table with the help of select statement. The query is as follows:

mysql> select *from FloorDemo;

Output:

Price
5.75
5.23
5.5

We have 3 records and we want the nearest integer. For that, use the FLOOR() function as we have discussed above.

The query is as follows that implmenets FLOOR() function :

mysql> SELECT FLOOR(Price) from FloorDemo;

Output:

FLOOR(Price)
5
5
5

SQL Roundup to Nearest Integer:

In MySQL, you can use the CEILING () function to round up a number to the nearest integer. Specifically, it returns the smallest integer greater than or equal to the argument.

You can also use the CEIL () function, which is a synonym for CEILING ().

Syntax :

CEILING (X) 

For numeric arguments with the exact value, the return value is a numeric type with the exact value. For strings or floating point arguments, the return value is of floating point type.

Example 1 – Positive Values

Here’s an example of providing a positive value as the argument.

SELECT CEILING(1.87) Result;

Result:

2

In this case, 2 is the smallest integer not less than 1.87.

Example 2: Here’s another example, this time with a larger value.

SELECT CEILING(200.87) Result;

Result:

201

Example 3 – Negative Values

Here’s an example using a negative value.

SELECT CEILING(-1.87) Result;

Result:

-1

In this case, -1 is the smallest integer not less than -1.87.

Example 4: Here’s another example.

SELECT CEILING(-200.87) Result;

Result:

-200

Example 5: The MySQL ROUND () function. The MySQL ROUND () function is used to round a number to the specified number of decimal places. If the rounding does not specify the number of decimal places, it is rounded to the nearest integer. X: Number to round 01:

Our database has a table named rent with data in the following columns: id, city, area, and bikes_for_rent.

id city area bikes_for_rent
1 Los Angeles 1302.15 1000
2 Phoenix 1340.69 500
3 Fargo 126.44 101

Let’s show each city’s name along with the ratio of its area to the number of bikes for rent. This ratio should be an integer.

SELECT city, 
CEILING(area/bikes_for_rent) AS ratio
FROM rent;

The query returns each city with the ratio as an integer of rounded up the area per one bike.

id city ratio
1 Los Angeles 2
2 Phoenix 3
3 Fargo 2

Discussion:

  • Like its counterpart floor, ceiling is a mathematical operation that takes a variety of and rounds it as much as the closest integer. For example, the ceiling of five is five, and so is the ceiling of 4.1.
  • SQL makes use of the CEILING characteristic to carry out this computation. It takes a unmarried argument: the column whose values you'd want to spherical as much as the closest integer.
  • In our example, we`d want to calculate what number of rectangular meters (rounded as much as the closest integer) there are in line with one bike. In our example, we used CEILING like so: CEILING(area/bikes_for_rent)). This returns an integer result, now no longer a float.

SQL Round Number

The ROUND() feature rounds various to a certain wide variety of decimal locations.

Syntax:

round_number::= 

Purpose:

  • ROUND returns n rounded to integer locations to the proper of the decimal point. If you leave out integer, then n is rounded to zero locations. The argument integer may be terrible to spherical off digits left of the decimal point.
  • n may be any numeric datatype or any nonnumeric datatype that may be implicitly transformed to a numeric datatype. The argument integer ought to be an integer. If you leave out integer, then the feature returns the equal datatype because the numeric datatype of the argument. If you encompass integer, then the feature returns NUMBER.
  • For NUMBER values, the price n is rounded farfar from zero (for example, to x+1 while x.five is effective and to x-1 while x.five is terrible). For BINARY_FLOAT and BINARY_DOUBLE values, the feature rounds to the closest even price. Please discuss with the examples that follow.

Example 1: The following example rounds a number to one decimal point:

SELECT ROUND(15.193,1) "Round" FROM DUAL;

Output:

Round
15.2

Example 2: The following example rounds a number one digit to the left of the decimal point:

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

Output:

Round
20

Example 3: The following examples illustrate the difference between rounding NUMBER and floating-point number values. NUMBER values are rounded up (for positive values), whereas floating-point numbers are rounded toward the nearest even value:

SELECT ROUND(1.5), ROUND(2.5) FROM DUAL;

Output:

Round(1.5) ROUND(2.5)
2 3
SELECT ROUND(1.5f), ROUND(2.5f) FROM DUAL;

Output:

Round(1.5F) ROUND(2.5F)
2.0E+000 2.0E+000

Example 4: Our database has a table named product with data in the following columns: id, name, and price_net.

id name price_net
1 bread 2.34
2 croissant 1.22
3 roll 0.68

Suppose there’s a tax of 24% on each product, and you’d like to compute the gross price of each item (i.e., after taxes) and round the value to two decimal places.

SELECT id, 
ROUND( price_net*1.24 , 2 ) as price_gross
FROM product;

This query returns the gross price rounded to two decimal places:

id price_gross
1 2.90
2 1.51
3 0.84

Discussion: If you want to round a SQL floating point number to the specified number of decimal places, use the ROUND function. The first argument to this function is the column for which you want to round the value. The second argument is optional and specifies the number of digits to round. If you omit the second argument, the function is rounded to the nearest integer by default.

Example 5: In this example, we won’t specify the number of places to which we want to round the column:

SELECT id, 
ROUND(price_net*1.24) as price_gross
FROM product;

Output:

id price_gross
1 3
2 2
3 1

SQL Round Remove Decimal Values

Decimal values ​​are values ​​that have "Float" as the data type.

There are several ways to remove decimal values ​​in SQL:

  • ROUND () function: This function in SQL Server is used to round a specified number to the specified number of decimal places.
  • Using the FLOOR () function: Returns the largest integer value less than or equal to a number.
  • Using the CAST () function: Explicit conversion must be performed in SQL Server using the Cast or Convert functions.

Consider the following simple example.

SELECT 'ROUNDdown', ROUND (5.1, 0) UNION ALL 
SELECT 'ROUND UP', ROUND (5.9, 0) UNION ALL 
SELECT 'TRIM ONLY', ROUND (5.1, 0, 1) UNION ALL 
SELECT 'TRIM ONLY', ROUND (5.9, 0, 1) 
GO

If you use the third parameter with a non-zero value, you just truncate the number of decimal places. This is very useful if you need to report numbers that do not include the number of decimal places, especially if you need a report. There is a total row with the correct values.

Example 1: ROUND () Here is one important thing to keep in mind when using the function.

SELECT ROUND(9.9, 0)
GO

Output:

Arithmetic overflow error

Are you surprised about this error?

The explanation is easy: value 9.9 is handled in this case as to be DECIMAL(2,1). Rounding it to 0 decimal places means that the new value will be 10 and this doesn’t fit into the original DECIMAL(2,1) data type.

This can be easily fixed like this:

SELECT ROUND(CAST(9.9 AS DECIMAL(3,1)), 0)
GO

I saw a lot of production disasters caused by this simple piece of code during my career.

Example 2:

STEP 1: Creating a database

Use the below SQL statement to create a database called table2:

CREATE DATABASE table2;

Step 2: Using the database

Use the below SQL statement to switch the database context to geeks:

USE geeks;

Step 3: Table definition

INSERT INTO tname VALUES ('ROMY',80.9),
('MEENAKSHI',86.89),('SHALINI',85.9),('SAMBHAVI', 89.45);

Step 5: Check value of the table

Content of the table can be viewed using the SELECT command.

SELECT * FROM tname;

Step 6: Use function to remove decimal values

By using Round() function

ROUND(): This function rounds a number to the specified number of decimal places. If you want to remove all decimal numbers, round to 0 in decimal.

Syntax:

ROUND(Value, decimal_place)

Query:

SELECT NAME, ROUND(MARKS,0) AS MARKS FROM tname;

80.0 is rounded to 81 as 81 is the nearest integer value.

Step 7: By using the FLOOR() function

FLOOR(): This function returns the largest integer value less than or equal to the value used as the parameter.

Syntax:

FLOOR(value)

Query:

SELECT NAME, FLOOR(MARKS) AS MARKS FROM tname;

Here, 80.9 receives transformed to 80, as FLOOR() returns a fee much less than or identical to the given fee however cannot go back the fee extra than the given one.

Step 8: By the usage of CAST() feature

CAST(): This feature is used to transform the fee into a particular records type.

Syntax:

CAST( value as datatype)

Query:

SELECT NAME, CAST(MARKS as INT) AS MARKS FROM tname;

This gives results similar to the FLOOR() function. Results vary slightly according to the function used. One should choose according to the need.


SQL Round Nearest Whole Number

I'm strolling SQL that wishes rounding up the price to the closest entire number.

What I want is 45.01 rounds as much as 46. Also 45.forty nine rounds to 46. And 45.ninety nine rounds as much as 46, too. I need the whole lot up one entire digit.

How do I acquire this in an UPDATE announcement just like the following?

Update product SET price=Round 

You should use the ceiling function; this part of SQL code:

select ceiling(45.01), ceiling(45.49), ceiling(45.99);

will get you "46" each time.

For your update, so, I'd say :

Update product SET price = ceiling(45.01)

BTW: On MySQL, ceil is an alias to ceiling ; not sure about other DB systems, so you might have to use one or the other, depending on the DB you are using...

Quoting the documentation :

CEILING(X)

Returns the smallest integer value not less than X.

And the given example :

mysql> SELECT CEILING(1.23);

Output:

2

mysql> SELECT CEILING(-1.23);

Output:

-1


SQL Round Time Nearest Hour

Rounds the specified date or time. If you omit the precision argument, ROUND is rounded to the precision (DD) of the day.

Syntax:

ROUND( rounding-target[, 'precision'] )

Parameters

rounding-target: An expression that evaluates to one of the following data types:

DATE

  • TIMESTAMP/TIMESTAMPTZ
  • TIMESTAMPTZ

precision

A string constant that specifies precision for the rounded value, one of the following:

  • Century: CC | SCC
  • Year: SYYY | YYYY | YEAR | YYY | YY | Y
  • ISO Year: IYYY | IYY | IY | I
  • Quarter: Q
  • Month: MONTH | MON | MM | RM
  • Same weekday as first day of year: WW
  • Same weekday as first day of ISO year: IW
  • Same weekday as first day of month: W
  • Day (default): DDD | DD | J
  • First weekday: DAY | DY | D
  • Hour: HH | HH12 | HH24
  • Minute: MI
  • Second: SS

Hour, minute, and second rounding is not supported by DATE expressions.

Example 1: Round to the nearest hour:

SELECT ROUND(CURRENT_TIMESTAMP, 'HH');

Output:

ROUND
2016-04-28 15:00:00

Example 2: Round to the nearest month:

SELECT ROUND('9-22-2011 12:34:00'::TIMESTAMP, 'MM');

Output:

ROUND
2011-10-01 00:00:00

Example 3: I was recently asked if I would like to create a function that rounds up the time based on the elapsed minute intervals. Well, not before, but today it's about how to create a function that rounds up the time to the nearest minute interval.

This is a very simple example of how to do the same.

CREATE FUNCTION [dbo].[RoundTime] (@Time DATETIME, @RoundToMin INT)
RETURNS DATETIME
AS
BEGIN
RETURN ROUND(CAST(CAST(CONVERT(VARCHAR,
@Time,121) AS DATETIME) AS FLOAT) * (1440/@RoundToMin),0)/(1440/@RoundToMin)
END
GO
GO

The above function takes two values. 1) Time to round up or down. 2) Time in minutes (the value passed here must be between 0 and 60. If the value is false, the result will be false.) The above function a) Validates the passed parameters b ) It can be expanded by adding functions such as accepting values. It will be 15 minutes, 30 minutes, and so on.

Here are few sample examples.

SELECT dbo.roundtime('17:29',30)
SELECT dbo.roundtime(GETDATE(),5)
SELECT dbo.roundtime('2012-11-02 07:27:07.000',15)

Running the above code returns the following result: Round up the time to the nearest minute interval.

SQLSERVER function Round time -- Now, is there another way to achieve the same result? If so, please share it here, and I will share it on my blog with legitimate credit.