How CEIL works in SQL? Best CEIL examples

How CEIL works in SQL? Best CEIL examples

When working with numbers in SQL, sometimes we need to round values up to the nearest whole number. That’s where the CEIL function comes in handy. If you’ve ever wondered how CEIL works in SQL and when to use it, you’re in the right place. Let’s dive in!

What is CEIL in SQL?

The CEIL function (short for Ceiling) rounds a given numeric value up to the nearest integer. Unlike standard rounding, where numbers round to the closest whole number based on decimal values, CEIL always pushes the number upwards.

CEIL Syntax

The syntax for using CEIL is simple:

CEIL(number)

Where:

  • number – A numeric expression that you want to round up.

It works with various numeric data types, including FLOAT, DECIMAL, and DOUBLE.

How CEIL Works in SQL – Examples

Let’s explore how the CEIL function behaves with some concrete examples.

Basic CEIL Usage

Here’s a basic example of the CEIL function in action:

SELECT CEIL(4.2) AS result;

Output:

result
5

Since 4.2 is not a whole number, CEIL rounds it up to 5.

More CEIL Examples

Let’s look at a few more cases:

SELECT 
    CEIL(7.7) AS ceil_1, 
    CEIL(6.1) AS ceil_2, 
    CEIL(-3.9) AS ceil_3, 
    CEIL(-6.2) AS ceil_4;

Output:

ceil_1 ceil_2 ceil_3 ceil_4
8 7 -3 -6

Key takeaways:

  • Positive numbers always round up to the next integer.
  • Negative numbers also round “up” (towards zero), meaning they become less negative.

CEIL with Table Data

Applying CEIL on database columns is a common use case. Take this example from a table named orders:

SELECT order_id, total_amount, CEIL(total_amount) AS rounded_amount FROM orders;

This query retrieves the original total amounts and their rounded-up values.

Where Should You Use CEIL?

The CEIL function is useful in various scenarios, such as:

  1. Rounding up prices: When ensuring a minimum price for products.
  2. Calculating page numbers: Dividing records into pages and needing complete page counts.
  3. Allocating resources: Distributing resources without under-estimating the need.

CEIL vs. FLOOR – What’s the Difference?

While CEIL rounds numbers up, FLOOR does the opposite:

SELECT CEIL(4.7) AS ceil_value, FLOOR(4.7) AS floor_value;

Output:

ceil_value floor_value
5 4

Use CEIL when you need to round up and FLOOR when rounding down.

Conclusion

The CEIL function in SQL is a simple but powerful tool for rounding numbers up. Whether you’re dealing with prices, pagination, or resource allocation, it ensures you always get whole numbers without underestimating values. Now that you know how CEIL works in SQL, try applying it to your database queries!

 

Other interesting article:

How ROUND works in SQL? Best ROUND examples