How to Round Decimal Values in SQL: ROUND, FLOOR, and CEILING Functions

When working with SQL, managing decimal values often involves rounding them for precise results. Whether you're reporting data, performing calculations, or formatting output, SQL provides three key functions to handle decimal rounding: ROUND, FLOOR, and CEILING. In this guide, you'll learn how these functions work, with practical examples to make it clear.

The ROUND Function: Standard Rounding

The ROUND function rounds a decimal value to the nearest whole number. If the fractional part of the number is 0.5 or higher, it rounds up. Otherwise, it rounds down. Let's break it down with an example.

Example:
Suppose you have the number 56.7. Since 0.7 is greater than 0.5, the ROUND function will return 57.
If the decimal part is less than 0.5, say 56.4, the result will be 56.

This function is helpful when you need results to follow traditional rounding rules, such as for financial calculations or user-friendly outputs.

Syntax for ROUND:

SELECT ROUND(decimal_value) FROM table_name;

Replace decimal_value with your column or number of choice, and you're good to go!

The CEILING Function: Always Rounding Up

Want to ensure a number always rounds up to the next whole number? That’s where CEILING comes in. This function doesn’t care about the size of the fractional part—if there’s any decimal value at all, it rounds up.

Example:
For the number 56.1, the CEILING function will return 57.
Even if the fractional part is very small, like 56.0001, CEILING will still round it up to 57.

This is especially useful for pricing systems or scenarios where you need a guaranteed "round-up" to cover limits or fees.

Syntax for CEILING:

SELECT CEILING(decimal_value) FROM table_name;

Use this function whenever rounding needs to favor upward adjustments.

The FLOOR Function: Always Rounding Down

If CEILING always rounds up, the FLOOR function does the opposite—it always rounds down. No matter the fractional part, FLOOR will take the number to the largest whole number less than or equal to it.

Example:
For the number 56.9, FLOOR will return 56.
Even if you have something like 56.9999, the result will remain 56.

This function is helpful when you need conservative rounding, such as in budgeting or inventory calculations.

Syntax for FLOOR:

SELECT FLOOR(decimal_value) FROM table_name;

This guarantees results that never exceed the actual value, especially useful for exact tolerances or limits.

Quick Comparison: ROUND vs CEILING vs FLOOR

Here’s a simple breakdown of how these functions behave with the same input, 56.7:

ROUND: Follows standard rounding rules. Result = 57.
CEILING: Always rounds up. Result = 57.
FLOOR: Always rounds down. Result = 56.

Understanding how each function operates ensures you choose the right tool for your data needs.

Conclusion

When managing decimal values in SQL, the ROUND, CEILING, and FLOOR functions are essential tools. Whether you need standard rounding, always rounding up, or always rounding down, these functions provide the flexibility you need for accurate calculations. Try them out with different values to see how they perform in real-world scenarios.