ROUND, CEIL, FLOOR in SQL

Introduction

These functions help you change numbers to simpler forms. You can round numbers, or always go up or down to the nearest whole number.

When you want to show prices without many decimals.

When you need to count whole items, not parts.

When you want to find the smallest whole number bigger than a value.

When you want to find the biggest whole number smaller than a value.

When you want to prepare numbers for reports or summaries.

Syntax

SQL

ROUND(number, decimals)
CEIL(number)
FLOOR(number)

ROUND changes a number to a set number of decimal places.

CEIL always rounds a number up to the next whole number.

FLOOR always rounds a number down to the previous whole number.

Examples

Rounds 3.14159 to 2 decimal places, result is 3.14.

SQL

SELECT ROUND(3.14159, 2);

Rounds 4.2 up to 5.

SQL

SELECT CEIL(4.2);

Rounds 4.8 down to 4.

SQL

SELECT FLOOR(4.8);

Rounds 123.456 to 0 decimals, result is 123.

SQL

SELECT ROUND(123.456, 0);

Sample Program

This query shows how ROUND, CEIL, and FLOOR work on the number 7.856.

SQL

SELECT ROUND(7.856, 1) AS rounded_value,
       CEIL(7.856) AS ceiling_value,
       FLOOR(7.856) AS floor_value;

Important Notes

ROUND can take a second number to decide how many decimals to keep.

CEIL and FLOOR always return whole numbers.

These functions are useful for cleaning up numbers before showing them.

Summary

ROUND changes numbers to a set decimal place.

CEIL always rounds numbers up.

FLOOR always rounds numbers down.