ROUND()

Syntax

How it works

ROUND() takes a number and shortens it to a chosen precision. In its simplest form, ROUND(number) rounds to the nearest integer, so ROUND(2.6) is 3 and ROUND(2.4) is 2. Add a second argument and you control how many decimal places survive: ROUND(3.14159, 2) gives 3.14.

The decimals argument can be negative, which rounds to the left of the decimal point. ROUND(1234, -2) rounds to the nearest hundred and returns 1200, while ROUND(1234, -1) returns 1230. This is handy for bucketing large numbers into tens, hundreds or thousands.

Where things get subtle is the halfway case, such as ROUND(2.5). Not every engine agrees. MySQL, SQL Server and SQLite use round half away from zero (so 2.5 becomes 3 and -2.5 becomes -3), while PostgreSQL rounds half to even, also called bankers rounding, for the double precision type (so 2.5 becomes 2 but 3.5 becomes 4). Because the outcome depends on both the engine and the column type, never assume a single rule. If you need rounding for money or reporting, pin down the behaviour on your database. ROUND is a sibling of CEIL() and FLOOR(), which always round up or down instead of to the nearest.

Examples

Round to 2 decimal places

SELECT ROUND(3.14159, 2) AS pi_2dp;

pi_2dp
------
  3.14

Round to the nearest whole number

-- No second argument means round to an integer
SELECT ROUND(2.6) AS up,
       ROUND(2.4) AS down;

up | down
---+-----
 3 |    2

Negative decimals round to the left

SELECT ROUND(1234, -1) AS nearest_ten,
       ROUND(1234, -2) AS nearest_hundred,
       ROUND(1250, -2) AS half_hundred;

nearest_ten | nearest_hundred | half_hundred
------------+-----------------+-------------
       1230 |            1200 |         1300

Round a currency total for display

SELECT order_id,
       ROUND(subtotal * 1.05, 2) AS total_with_tax
FROM orders
ORDER BY order_id;

order_id | total_with_tax
---------+---------------
       1 |          21.00
       2 |          13.65
       3 |         104.32

Round then group into price buckets

-- Bucket products into 100 wide price bands
SELECT ROUND(price, -2) AS price_band,
       COUNT(*)         AS products
FROM products
GROUP BY ROUND(price, -2)
ORDER BY price_band;

price_band | products
-----------+---------
         0 |       14
       100 |       38
       200 |       21

Common mistakes

-- Assuming ROUND(2.5) is always 3 everywhere.
-- On PostgreSQL double precision it can be 2.
SELECT ROUND(2.5), ROUND(3.5);

-- Cast to numeric on PostgreSQL for predictable,
-- round half away from zero behaviour
SELECT ROUND(2.5::numeric, 0),
       ROUND(3.5::numeric, 0);

-- Rounding for storage throws away real precision.
-- Later math compounds the lost accuracy.
UPDATE line_items
SET unit_price = ROUND(unit_price, 2);

-- Store full precision, round only when you display
SELECT ROUND(unit_price, 2) AS display_price
FROM line_items;

-- Surprised that a float does not round cleanly
SELECT ROUND(0.1 + 0.2, 1);  -- may show 0.3 but the
                             -- stored value is 0.30000000000000004

-- Use an exact decimal type for money and rounding
SELECT ROUND(CAST(0.1 AS DECIMAL(10,2))
           + CAST(0.2 AS DECIMAL(10,2)), 1);

Performance

ROUND() is a lightweight scalar operation that runs once per row, so on its own it costs almost nothing. The usual concern is not ROUND itself but where you put it: rounding a column inside a WHERE clause or a JOIN condition, such as WHERE ROUND(price, 0) = 10, prevents the optimiser from using an index on that column, because the stored value no longer matches the expression.

When you round inside a GROUP BY to build buckets, the database must evaluate the expression for every row before grouping. On large tables that is fine for reports, but if you bucket the same way repeatedly, consider a computed or persisted column so the rounded value can itself be indexed.

Prefer rounding in the final projection (the SELECT list) rather than deep inside filters. Keep the raw value available so an index can still be used to locate rows, then round the result on the way out.

Interview questions

SELECT ROUND(2.5), ROUND(3.5);