Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT ROUND(price * 1.2, 2) AS price_with_tax
FROM products;

Try it

SQL Rounding prices and averages ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Halves, NULL and the type of the result

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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) gives 2.67 in SQLite because 2.675 is stored as a binary value slightly below 2.675. For money, store integer cents or a DECIMAL/NUMERIC type where your database has one.
  • Rounding is not formatting. ROUND(3, 2) prints 3, not 3.00. To show a fixed number of decimals use printf() or FORMAT().
  • Truncate instead of round with CAST(x AS INTEGER) (toward zero) or FLOOR(); the vendor TRUNC/TRUNCATE functions 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.
  • NULL in, NULL out.