SQL Reference ยท Scalar Functions
SQL ROUND() function
Reference for the SQL ROUND() function: round numbers to a number of decimal places, how halves and floating point behave, and SQLite versus other databases.
ROUND(x, digits) rounds a number to the given number of decimal places and returns the result. With one argument it rounds to a whole number. It is the everyday tool for presenting prices, averages and percentages.
Rounding changes the value that is returned, not the value that is stored. Round when you display or report a number, and keep full precision for calculations in between; rounding early makes small errors pile up.
SELECT ROUND(price * 1.2, 2) AS price_with_tax FROM products;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
ROUND(x) ROUND(x, digits)
| Name | Description |
|---|---|
x |
The number to round. Text that looks like a number is converted; anything else becomes 0 in SQLite. |
digits |
Optional. How many decimal places to keep. 0 (the default) gives a whole number. |
Returns: the rounded number, or NULL if x is NULL. In SQLite the result is always a floating-point value, so ROUND(5) is 5.0 internally even though it prints as 5.
Dialect differences
| Database | Behavior |
|---|---|
| SQLite | ROUND(x [, digits]); halves round away from zero; negative digits are treated as 0. |
| PostgreSQL | ROUND(x [, digits]); negative digits round to tens, hundreds and so on. For numeric values halves go away from zero; for double precision the result follows the floating-point rounding mode (usually to the nearest even). |
| MySQL | ROUND(x [, digits]); exact values (DECIMAL) round halves away from zero, approximate values (FLOAT, DOUBLE) may round halves to even. Negative digits allowed. |
| SQL Server | ROUND(x, digits [, function]): digits is required, and a non-zero third argument truncates instead of rounding. Negative digits allowed. |
| Oracle | ROUND(x [, digits]); halves away from zero; negative digits allowed. Also rounds dates. |
-- PostgreSQL, MySQL, Oracle: round to the nearest hundred SELECT ROUND(1234.5678, -2); -- 1200 -- SQL Server: truncate to two decimals instead of rounding SELECT ROUND(2.678, 2, 1); -- 2.670
In SQLite, round to hundreds with ROUND(x / 100.0) * 100.
Usage notes
- Floating point surprises.
ROUND(2.675, 2)gives2.67in SQLite because2.675is stored as a binary value slightly below 2.675. For money, store integer cents or aDECIMAL/NUMERICtype where your database has one. - Rounding is not formatting.
ROUND(3, 2)prints3, not3.00. To show a fixed number of decimals useprintf()orFORMAT(). - Truncate instead of round with
CAST(x AS INTEGER)(toward zero) orFLOOR(); the vendorTRUNC/TRUNCATEfunctions are not available in SQLite. - Use it on aggregates, not before them:
ROUND(AVG(price), 2)is right;AVG(ROUND(price))averages already-damaged values. - Do not compare rounded floats with
=unless both sides were rounded the same way. - Round half up for positive numbers is the common expectation, but it is not guaranteed across databases for floating-point types; test with your own data.
NULLin,NULLout.