SQL Academy · Lesson

Rounding and Truncating

ROUND, CEIL, FLOOR and TRUNC.

Rounding and Truncating is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Rounding Matters

When working with numbers in SQL, you often get results with many decimal places. For example, dividing 10 by 3 gives 3.3333333...

SQL provides built-in functions to control how many decimal places you display or store: ROUND, CEIL, FLOOR, and TRUNC. These are essential for presenting clean financial figures, measurements, and statistics.

SELECT 10.0 / 3.0 AS raw_result;

ROUND: Basic Usage

The ROUND() function rounds a number to a specified number of decimal places. It follows standard rounding rules: digits 5 and above round up, digits below 5 round down.

The syntax is: ROUND(number, decimal_places). If you omit the second argument, it rounds to the nearest whole number.

SELECT
  ROUND(3.14159, 2) AS two_decimals,
  ROUND(3.14159, 0) AS zero_decimals,
  ROUND(3.14159)    AS no_arg;

ROUND with Negative Places

A lesser-known feature of ROUND() is that you can pass a negative number as the second argument. This rounds to the left of the decimal point — useful for rounding to the nearest ten, hundred, or thousand.

For example, ROUND(1567, -2) rounds to the nearest hundred and gives 1600.

SELECT
  ROUND(1567.89, -1)  AS nearest_ten,
  ROUND(1567.89, -2)  AS nearest_hundred,
  ROUND(1567.89, -3)  AS nearest_thousand;

CEIL: Always Round Up

CEIL() (also written as CEILING() in some databases) always rounds a number up to the nearest integer, regardless of the decimal portion.

This is useful when you need to ensure you never go below a minimum — for example, calculating the number of pages needed to display a set of results.

SELECT
  CEIL(4.1)   AS ceil_4_1,
  CEIL(4.9)   AS ceil_4_9,
  CEIL(-4.1)  AS ceil_neg_4_1,
  CEIL(-4.9)  AS ceil_neg_4_9;

FLOOR: Always Round Down

FLOOR() always rounds a number down to the nearest integer, regardless of the decimal portion.

Notice that for negative numbers, FLOOR rounds away from zero — FLOOR(-4.1) gives -5, not -4. This is the opposite behaviour from what some beginners expect.

SELECT
  FLOOR(4.9)   AS floor_4_9,
  FLOOR(4.1)   AS floor_4_1,
  FLOOR(-4.1)  AS floor_neg_4_1,
  FLOOR(-4.9)  AS floor_neg_4_9;

CEIL vs FLOOR Side by Side

Seeing CEIL and FLOOR together makes it easy to understand the difference. CEIL always goes up to the next whole number, while FLOOR always goes down.

Think of it like a building: FLOOR is the ground level you fall to, CEIL is the ceiling you rise to.

SELECT
  2.3   AS original,
  CEIL(2.3)  AS ceil_result,
  FLOOR(2.3) AS floor_result
UNION ALL
SELECT
  7.8,
  CEIL(7.8),
  FLOOR(7.8);

TRUNC: Cut Without Rounding

TRUNC() (or TRUNCATE() in MySQL) removes decimal digits without any rounding. It simply chops off the digits after the specified position.

This is different from ROUND — TRUNC(4.9) gives 4, not 5. The decimal part is just discarded.

SELECT
  TRUNC(4.9)       AS trunc_4_9,
  TRUNC(4.1)       AS trunc_4_1,
  TRUNC(-4.9)      AS trunc_neg_4_9,
  TRUNC(3.14159, 3) AS trunc_3_decimals;

ROUND vs TRUNC Compared

The key difference between ROUND and TRUNC is that ROUND changes the value based on the next digit, while TRUNC always discards everything after the cut point without adjustment.

Use ROUND when you want the mathematically closest value. Use TRUNC when you want to strictly remove precision without any upward adjustment — common in financial calculations where you should never overpay.

SELECT
  ROUND(2.567, 1)  AS rounded,
  TRUNC(2.567, 1)  AS truncated;
-- ROUND gives 2.6 (5 rounds up)
-- TRUNC gives 2.5 (just cuts off)

Using ROUND in a Real Table

Rounding functions are most powerful when applied to table columns. Here we create a simple products table with prices and apply ROUND to display them cleanly.

This pattern is very common in e-commerce and accounting queries where raw calculations produce many decimal places.

CREATE TABLE products (
  name  TEXT,
  price NUMERIC
);

INSERT INTO products VALUES
  ('Widget',  9.999),
  ('Gadget',  24.3333),
  ('Doohickey', 4.5678);

SELECT name, ROUND(price, 2) AS display_price
FROM products;

Combining Functions in a Query

You can combine rounding functions with other SQL expressions. For example, calculate a discounted price, then round it for display.

This query applies a 15% discount, rounds the result to 2 decimal places, and also shows the FLOOR and CEIL of the discounted price — giving you minimum and maximum whole-number prices.

SELECT
  name,
  price,
  ROUND(price * 0.85, 2)  AS discounted,
  FLOOR(price * 0.85)     AS min_price,
  CEIL(price  * 0.85)     AS max_price
FROM products;

Pagination with CEIL

A classic use of CEIL is calculating total pages in a pagination system. If you have 97 rows and show 10 per page, you need CEIL(97 / 10.0) = 10 pages (not 9, because the last page has 7 rows).

Always divide by a decimal (10.0 not 10) to avoid integer division, which would discard the remainder before CEIL can act on it.

SELECT
  97 AS total_rows,
  10 AS rows_per_page,
  CEIL(97 / 10.0) AS total_pages;

Quick Check

Test your understanding of SQL rounding functions.

Lesson Recap

Great work! Here is a summary of the rounding and truncating functions in SQL:

ROUND(n, d) — rounds to d decimal places using standard math rules (5+ rounds up).
CEIL(n) — always rounds up to the nearest integer.
FLOOR(n) — always rounds down to the nearest integer.
TRUNC(n, d) — removes digits after position d without rounding; never increases the value.

Use ROUND for display and statistics, CEIL for minimum-capacity calculations (like pages), FLOOR for maximum-fits calculations, and TRUNC when you must never round upward — especially in financial contexts.

Frequently asked questions

Is the “Rounding and Truncating” lesson free?

Yes — the full text of “Rounding and Truncating” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Rounding and Truncating”?

ROUND, CEIL, FLOOR and TRUNC. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Rounding and Truncating” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy