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)

-- Compare rounding methods by applying a 10% tax rate to product prices.
SELECT
    product_name,
    unit_price,
    ROUND(unit_price * 1.1, 0)    AS price_with_tax_round,
    CEIL(unit_price * 1.1)        AS price_with_tax_ceil,
    FLOOR(unit_price * 1.1)       AS price_with_tax_floor,
    TRUNCATE(unit_price * 1.1, 0) AS price_with_tax_truncate
FROM products;

-- Display monthly sales rounded to the nearest thousand.
SELECT
    sales_month,
    SUM(sales_amount) AS total,
    ROUND(SUM(sales_amount), -3) AS rounded_to_thousands
FROM monthly_sales
GROUP BY sales_month
ORDER BY sales_month;

-- Calculate the average unit price rounded to one decimal place.
SELECT
    category_name,
    ROUND(AVG(unit_price), 1) AS avg_unit_price
FROM products
GROUP BY category_name;

-- Tax-inclusive price comparison (unit price 1980, multiplied by 1.1 = 2178.0).
product_name | unit_price | round | ceil | floor | truncate
-------------+------------+-------+------+-------+---------
Mug          | 1980       | 2178  | 2178 | 2178  | 2178
Notebook     | 1500       | 1650  | 1650 | 1650  | 1650
Ballpoint Pen| 980        | 1078  | 1078 | 1078  | 1078

-- Monthly sales rounded to the nearest thousand.
sales_month | total     | rounded_to_thousands
------------+-----------+---------------------
2024-01     | 1234567   | 1235000
2024-02     | 987654    | 988000

'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'.