SQL Math Functions

SQL ROUND, CEIL, FLOOR: Round Numbers (All Databases)

Round numbers in SQL with ROUND, CEILING/CEIL, FLOOR, and TRUNCATE. Covers decimal precision, banker rounding, and practical examples.

Mar 12, 20264 min read

Overview

ROUND rounds to the nearest value, CEIL rounds up, FLOOR rounds down. These functions work the same across nearly all databases.

All Databases

-- ROUND(number, decimals):
SELECT ROUND(3.14159, 2);   -- 3.14
SELECT ROUND(3.145, 2);     -- 3.15 (or 3.14 in banker's rounding)
SELECT ROUND(3.14159, 0);   -- 3
SELECT ROUND(3.14159);      -- 3 (default 0 decimals)
SELECT ROUND(1234, -2);     -- 1200 (round to hundreds)

-- CEILING / CEIL (round up):
SELECT CEIL(3.1);    -- 4
SELECT CEIL(3.9);    -- 4
SELECT CEIL(-3.1);   -- -3

-- FLOOR (round down):
SELECT FLOOR(3.9);   -- 3
SELECT FLOOR(3.1);   -- 3
SELECT FLOOR(-3.1);  -- -4

-- Practical examples:
SELECT
  product,
  price,
  ROUND(price * 0.9, 2) AS discounted_price,
  CEIL(quantity / 12.0) AS boxes_needed
FROM orders;

-- TRUNCATE (cut decimals without rounding):
-- MySQL: TRUNCATE(3.149, 2) → 3.14
-- PostgreSQL: TRUNC(3.149, 2) → 3.14
-- SQL Server: ROUND(3.149, 2, 1) → 3.14 (3rd arg = truncate)

Skip the Syntax Lookup

Instead of memorizing ROUND / CEIL / FLOOR syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Try AI2SQL Free

No credit card required

Frequently Asked Questions

How do I round to 2 decimal places in SQL?

Use ROUND(number, 2). This works in all databases. Example: ROUND(99.999, 2) returns 100.00.

What is the difference between ROUND, CEIL, and FLOOR?

ROUND goes to nearest. CEIL always rounds up (3.1 → 4). FLOOR always rounds down (3.9 → 3). For negative numbers: CEIL(-3.1) = -3, FLOOR(-3.1) = -4.

Can AI2SQL round numbers in queries?

Yes. Say 'calculate average price rounded to 2 decimal places by category' and AI2SQL generates the correct ROUND query.