Earn income with your data and sql skills
We're building the largest freelancing marketplace for people like you.

# 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).

### #

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.

## ROUND to WHOLE NUMBERS.

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
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.

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

Last updated on Dec 21, 2023

Earn income with your data and sql skills