ROUND / CEIL / FLOOR

ROUND: Rounds a number to the specified number of decimal places.

ROUND(number, digits)

CEIL / CEILING: Returns the smallest integer greater than or equal to the number (ceiling).

CEIL(number)
CEILING(number)

FLOOR: Returns the largest integer less than or equal to the number (floor).

FLOOR(number)

TRUNCATE: Truncates the decimal part to the specified number of digits (MySQL, etc.).

TRUNCATE(number, digits)

The following examples use the products table.

Compare rounding methods by applying a 10% tax rate to product prices.

sample_round_ceil_floor.sql

SELECT
    product_name,
    unit_price,
    ROUND(unit_price * 1.1, 0)    AS price_round,
    CEIL(unit_price * 1.1)        AS price_ceil,
    FLOOR(unit_price * 1.1)       AS price_floor,
    TRUNCATE(unit_price * 1.1, 0) AS price_truncate
FROM products;

+---------------+------------+-------------+------------+-------------+----------------+
| product_name  | unit_price | price_round | price_ceil | price_floor | price_truncate |
+---------------+------------+-------------+------------+-------------+----------------+
| Mug           |       1980 |        2178 |       2178 |        2178 |           2178 |
| Notebook      |       1500 |        1650 |       1650 |        1650 |           1650 |
| Ballpoint Pen |        980 |        1078 |       1078 |        1078 |           1078 |
+---------------+------------+-------------+------------+-------------+----------------+
3 rows in set

Display monthly sales rounded to the nearest thousand.

sample_round_ceil_floor.sql

SELECT
    sales_month,
    SUM(sales_amount) AS total,
    ROUND(SUM(sales_amount), -3) AS total_rounded
FROM monthly_sales
GROUP BY sales_month
ORDER BY sales_month;

+-------------+---------+---------------+
| sales_month | total   | total_rounded |
+-------------+---------+---------------+
|     2024-01 | 1234567 |       1235000 |
|     2024-02 |  987654 |        988000 |
+-------------+---------+---------------+
2 rows in set

Calculate the average unit price rounded to one decimal place.

sample_round_ceil_floor.sql

SELECT
    category_name,
    ROUND(AVG(unit_price), 1) AS avg_unit_price
FROM products
GROUP BY category_name;

+---------------+----------------+
| category_name | avg_unit_price |
+---------------+----------------+
| Goods         |         1486.7 |
+---------------+----------------+
1 row in set

ROUND, CEIL (or CEILING), and FLOOR are supported across MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.

-- Syntax that works across all databases.
SELECT ROUND(unit_price * 1.1, 0) AS price_with_tax_round FROM products;
SELECT CEIL(unit_price * 1.1) AS price_with_tax_ceil FROM products;
SELECT FLOOR(unit_price * 1.1) AS price_with_tax_floor FROM products;

In Oracle, only CEIL is available — CEILING is not supported. All other databases accept both forms.

TRUNCATE is a MySQL-specific function. In PostgreSQL and Oracle, use TRUNC instead. In SQL Server, passing 1 as the third argument to ROUND performs truncation.

-- MySQL: Use TRUNCATE.
SELECT TRUNCATE(unit_price * 1.1, 0) AS price_with_tax_floor FROM products;

-- PostgreSQL / Oracle: Use TRUNC.
SELECT TRUNC(unit_price * 1.1, 0) AS price_with_tax_floor FROM products;

-- SQL Server: Pass 1 as the third argument to ROUND.
SELECT ROUND(unit_price * 1.1, 0, 1) AS price_with_tax_floor FROM products;

The second argument of ROUND accepts negative values. For example, -1 rounds to the nearest ten, and -3 rounds to the nearest thousand.

Pay attention to how CEIL and FLOOR handle negative numbers. CEIL(-1.5) returns -1 (toward zero), while FLOOR(-1.5) returns -2 (away from zero). For tax calculations, choose rounding, ceiling, or floor according to the applicable laws and business rules. In Japan, truncation (floor) is the standard method for consumption tax.

For other numeric operations such as absolute value and modulo, see ABS / MOD / POWER.