Round Numbers With ROUND, CEIL, and FLOOR

Control the precision or snapping of numeric results using `ROUND` for half-up rounding, `CEIL` to round up to the next integer, and `FLOOR` to round down.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Round, floor, and ceil the unit cost of each order

100.00 / 3 = 33.333...: ROUND to 2 places gives 33.33, FLOOR discards the fraction to give 33, CEIL bumps up to 34. 50.00 / 4 = 12.5: ROUND to 2 places gives 12.50 (already at 2 decimal places), FLOOR gives 12, CEIL gives 13. 75.00 / 10 = 7.5: same logic as 12.5.

MySQL MariaDB PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, total DECIMAL(10, 2), qty INT);

INSERT INTO
  orders
VALUES
  (1, 100.00, 3),
  (2, 50.00, 4),
  (3, 75.00, 10);

Validated query

SQL

SELECT
  id,
  ROUND(total / qty, 2) AS unit_cost,
  FLOOR(total / qty) AS floor_cost,
  CEIL(total / qty) AS ceil_cost
FROM
  orders
ORDER BY
  id;

Expected result

id	unit_cost	floor_cost	ceil_cost
1	33.33	33	34
2	12.50	12	13
3	7.50	7	8

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, total DECIMAL(10, 2), qty INT);

INSERT INTO
  orders
VALUES
  (1, 100.00, 3),
  (2, 50.00, 4),
  (3, 75.00, 10);

Validated query

SQL

SELECT
  id,
  ROUND(total / qty, 2) AS unit_cost,
  FLOOR(total / qty) AS floor_cost,
  CEILING(total / qty) AS ceil_cost
FROM
  orders
ORDER BY
  id;

Expected result

id	unit_cost	floor_cost	ceil_cost
1	33.33	33.00	34.00
2	12.50	12.00	13.00
3	7.50	7.00	8.00

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE orders (id INT, total DECIMAL(10, 2), qty INT);

INSERT INTO
  orders
VALUES
  (1, 100.00, 3),
  (2, 50.00, 4),
  (3, 75.00, 10);

Validated query

SQL

SELECT
  id,
  ROUND(total / qty, 2) AS unit_cost,
  FLOOR(total / qty) AS floor_cost,
  CEIL(total / qty) AS ceil_cost
FROM
  orders
ORDER BY
  id;

Expected result

id	unit_cost	floor_cost	ceil_cost
1	33.33	33	34
2	12.5	12	13
3	7.5	7	8

SQL Server uses CEILING and preserves the DECIMAL scale on FLOOR/CEILING results. SQLite returns real values. Numeric values are identical across all engines.

Useful when

Where this command helps.

rounding monetary values to two decimal places before display or storage
binning a continuous measurement into integer or 10-unit buckets

Explanation

What the command is doing.

ROUND(n, d) rounds value n to d decimal places using round-half-away-from-zero. A negative d rounds to the left of the decimal — ROUND(1234, -2) returns 1200. FLOOR(n) returns the largest integer not greater than n; CEIL(n) (or CEILING(n) in SQL Server) returns the smallest integer not less than n. All three functions accept negative inputs and are supported across every major engine.

Round Numbers With ROUND, CEIL, and FLOOR

Round, floor, and ceil the unit cost of each order

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.