# Rounding Down with ROUND() function

I am wanting to round down these numbers, and I have tried several different `ROUND()` functions but can not get it exactly right. Take this sample DDL

``````Create Table #Fun
(
value1 float
)

Insert Into #Fun Values
(1002313), (1888222), (1666222), (1222333)

Select
ROUND([value1], -5) As Rounded
FROM #Fun

Drop Table #Fun
``````

In the below the left column is what SQL produces and the right is what I want it to be. What must be altered in SQL in order to provide the output I am requesting?

``````1000000 = is fine
1900000 = 2000000
1700000 = 1500000
1200000 = 1000000
``````

It actually looks like you're trying to get the nearest 500000.

In your case, use `ROUND(value1/500000.0,0)*500000`

While it appears you want to round to the nearest 500000 and @Forrest's answer is correct for that I thought I would throw in an answer for the stated question. If you always want to round down then use `FLOOR` instead of `ROUND`. Unfortunately `FLOOR` doesn't have a second parameter so you have to use the division trick Forrest mentioned.

``````CREATE TABLE #Fun
(
value1 float
)

INSERT INTO #Fun VALUES
(1002313), (1888222), (1666222), (1222333)

SELECT ROUND([value1], -5) As Rounded, FLOOR([value1]/100000)*100000 AS Floored,
FLOOR([value1]/500000)*500000 AS Floored500k
FROM #Fun

DROP TABLE #Fun
``````

Output

``````Rounded Floored Floored500k
1000000 1000000 1000000
1900000 1800000 1500000
1700000 1600000 1500000
1200000 1200000 1000000
``````