How do I round numbers with SQL?

  1. Rounding functions are pretty much identical to the ones that you have, most likely, already used in Excel

2. They all have identical syntax in the DBs we’ve been using so far – Vertica, MySQL, SQL Server, PostgreSQL

-----------------------Vertica-----------------------------------------------------------------------------------------------
select
ceiling(285.32) as ceiling, -- rounds up to the closest higher integer
floor(285.82) as floor, -- rounds down to the closest lower integer
285.253 as number1,
round(285.253,0) as roundless5to0, -- rounds to 0 decimal points
round(285.253,1) as roundless5to1, -- rounds to 1 decimal point
round(285.253,2) as roundless5to2, -- rounds to 2 decimal points
285.753 as number2,
round(285.753,0) as roundmore5to0, -- rounds to 0 decimal points
round(285.753,1) as roundmore5to1, -- rounds to 1 decimal point
round(285.753,2) as roundmore5to2 -- rounds to 2 decimal points
-----------------------SQL Server----------------------------------------------------------------------------------------------
select
ceiling(285.32) as ceiling, -- rounds up to the closest higher integer
floor(285.82) as floor, -- rounds down to the closest lower integer
285.253 as number1,
round(285.253,0) as roundless5to0, -- rounds to 0 decimal points
round(285.253,1) as roundless5to1, -- rounds to 1 decimal point
round(285.253,2) as roundless5to2, -- rounds to 2 decimal points
285.753 as number2,
round(285.753,0) as roundmore5to0, -- rounds to 0 decimal points
round(285.753,1) as roundmore5to1, -- rounds to 1 decimal point
round(285.753,2) as roundmore5to2 -- rounds to 2 decimal points
ceiling |floor |number1 |roundless5to0 |roundless5to1 |roundless5to2 |number2 |roundmore5to0 |roundmore5to1 |roundmore5to2 |
--------|------|--------|--------------|--------------|--------------|--------|--------------|--------------|--------------|
286 |285 |285.253 |285.000 |285.300 |285.250 |285.753 |286.000 |285.800 |285.750 |
-----------------------MySQL----------------------------------------------------------------------------------------------------
select
ceiling(285.32) as ceiling, -- rounds up to the closest higher integer
floor(285.82) as floor, -- rounds down to the closest lower integer
285.253 as number1,
round(285.253,0) as roundless5to0, -- rounds to 0 decimal points
round(285.253,1) as roundless5to1, -- rounds to 1 decimal point
round(285.253,2) as roundless5to2, -- rounds to 2 decimal points
285.753 as number2,
round(285.753,0) as roundmore5to0, -- rounds to 0 decimal points
round(285.753,1) as roundmore5to1, -- rounds to 1 decimal point
round(285.753,2) as roundmore5to2 -- rounds to 2 decimal points
ceiling |floor |number1 |roundless5to0 |roundless5to1 |roundless5to2 |number2 |roundmore5to0 |roundmore5to1 |roundmore5to2 |
--------|------|--------|--------------|--------------|--------------|--------|--------------|--------------|--------------|
286 |285 |285.253 |285 |285.3 |285.25 |285.753 |286 |285.8 |285.75 |
-----------------------PostgreSQL-------------------------------------------------------------------------------------------------
select
ceiling(285.32) as ceiling, -- rounds up to the closest higher integer
floor(285.82) as floor, -- rounds down to the closest lower integer
285.253 as number1,
round(285.253,0) as roundless5to0, -- rounds to 0 decimal points
round(285.253,1) as roundless5to1, -- rounds to 1 decimal point
round(285.253,2) as roundless5to2, -- rounds to 2 decimal points
285.753 as number2,
round(285.753,0) as roundmore5to0, -- rounds to 0 decimal points
round(285.753,1) as roundmore5to1, -- rounds to 1 decimal point
round(285.753,2) as roundmore5to2 -- rounds to 2 decimal points
ceiling |floor |number1 |roundless5to0 |roundless5to1 |roundless5to2 |number2 |roundmore5to0 |roundmore5to1 |roundmore5to2 |
--------|------|--------|--------------|--------------|--------------|--------|--------------|--------------|--------------|
286 |285 |285.253 |285 |285.3 |285.25 |285.753 |286 |285.8 |285.75 |

Happy SQL-ing!