SQL ROUND Function

ROUND returns a rounded number with a given precision.

ROUND always returns a value. If there is an error, it returns 0.

This function can round, but it can also truncate (with an optional parameter).

Example

#

This example return rounded numbers with 0 decimal places.

SELECT ROUND(36.2, 0) AS '36.2',
       ROUND(36.5, 0) AS '36.5',
       ROUND(36.8, 0) AS '36.8' 
Result:  1 record
36.2 36.5 36.8
36.0 37.0 37.0

Note: ROUND returns the remaining decimal places as 0.


Syntax

Syntax for the ROUND function.

ROUND (number, length [,function ])

number -- a decimal value or column name.

length -- specifies the precision to which the input is rounded.

function -- optional. If 0, value will be rounded. If not 0, value will be truncated.


More Examples

ROUND to WHOLE NUMBERS.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products with prices rounded to whole numbers. Sort by name in descending order.
 SELECT ProductName, UnitPrice,
        ROUND(UnitPrice, 0) AS 'Rounded Price'
   FROM Product
  ORDER BY Productname DESC  
Result:  78 records
ProductName UnitPrice Rounded Price
Zaanse koeken 9.50 10.00
Wimmers gute Semmelknödel 33.25 33.00
Vegie-spread 43.90 44.00
Valkoinen suklaa 10.00 10.00
Chef Anton's Cajun Seasoning 16.25 16.00
Uncle Bob's Organic Dried Pears 30.00 30.00

ROUND. Truncate.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products with rounded and truncated prices to nearest $10 units.
SELECT ProductName, UnitPrice,
       ROUND(UnitPrice, -1, 0) AS 'Rounded Price',
       ROUND(UnitPrice, -1, 1) AS 'Truncated Price'
  FROM Product
Result:  78 records
ProductName UnitPrice Rounded Price Truncated Price
Chai 18.00 20.00 10.00
Chang 19.00 20.00 10.00
Aniseed Syrup 10.00 10.00 10.00
Chef Anton's Cajun Seasoning 22.00 20.00 20.00
Chef Anton's Cajun Seasoning 22.00 20.00 20.00
Chef Anton's Gumbo Mix 21.35 20.00 20.00

You may also like



Guides


vsn 3.1