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!