ROUND()
SQL · Math Functions
Theory
ROUND rounds a number to a chosen number of decimal places. ROUND(3.14159, 2) gives 3.14, ROUND(2.5) gives 3, and ROUND(salary / 12.0, 2) gives a monthly salary in rupees and paise. If you leave out the second argument it rounds to a whole number. Most databases also accept a negative second argument to round to tens, hundreds or thousands, such as ROUND(48750, -3) = 49000, but SQLite does not support that.
Two details matter in practice. First, integer division: in SQLite, PostgreSQL and SQL Server, 50000 / 12 is integer division and gives 4166 before ROUND even runs, so write 12.0 to keep the decimals. Second, decimal numbers stored as floating point are not always exact, so a value that looks like 2.675 may really be 2.67499999 and round down. Use DECIMAL/NUMERIC types for money, and round only in the final output.
Why we use it
Raw calculations produce long decimals (41666.666666...) that are hard to read and look wrong on invoices or dashboards. ROUND presents numbers at a sensible precision such as two decimals for currency.
When to use it
Use ROUND in the final SELECT for display and reporting. Avoid rounding in intermediate steps, because errors add up. Use CEIL or FLOOR when you always need to round in one direction, not to the nearest value.
How it works
- Number and number of decimals, e.g. (41666.667, 2)
- Integer / integer division? Yes: Use 12.0 so decimals are kept. No: Continue.
- Look at the digit after the last kept place
- Is that digit 5 or more? Yes: Round the kept part up. No: Round the kept part down.
- Return the rounded number
Syntax
ROUND(number, decimals)Example
SELECT ROUND(salary / 12.0, 2) FROM employees;Key points
- Omitting decimals rounds to the nearest whole number
- Half-rounding behavior can vary slightly by database
- Integer / integer drops decimals in many databases — divide by 12.0
- Negative decimals (ROUND(x, -3)) round to thousands in most databases, not SQLite
- Use DECIMAL for money; floating point can make 2.675 round to 2.67