MySQL CEIL() Function — Detailed Study Note

1. Overview

The MySQL CEIL() function is a mathematical function used to round a numeric value up to the nearest integer.

It always returns the smallest integer greater than or equal to the specified number, regardless of whether the number is positive or negative.

If you’re working with floating-point numbers and you need to round up to a whole number, CEIL() (or its synonym CEILING()) is the right function to use.

2. Syntax

CEIL(numeric_expression);

Parameters

• numeric_expression:

This can be:

1. A literal numeric value (e.g., 1.23, 5.8)
2. A column containing numeric data (e.g., price, salary)
3. A mathematical expression that returns a numeric result (e.g., price * 1.15)

3. Return Type

• The data type of the return value depends on the input type:
• If the input is an integer, the result will also be an integer.
• If the input is a floating-point number, the result will be a floating-point type (though the decimal part will be .0 since it’s rounded up).

4. Key Notes

• CEIL() and CEILING() are synonyms; both work exactly the same way.

Example:

Copy

SELECT CEIL(2.3), CEILING(2.3);

Both return 3.

• Unlike the ROUND() function, which rounds to the nearest integer

CEIL() always rounds up, regardless of the decimal value.

• The direction of rounding depends on whether the number is positive or negative:
• For positive numbers, it moves up to the next integer.
• For negative numbers, it moves toward zero (less negative).

5. Illustration

6. Examples

Example 1 – Using CEIL() with a Positive Number

SELECT CEIL(1.59) AS Result;

Output:

Result
-------
2

Explanation:

1.59 is rounded up to the nearest integer, which is 2.

Example 2 – Using CEIL() with a Negative Number

SELECT CEIL(-1.59) AS Result;

Output:

Result
-------
-1

Explanation:

Although -1.59 is less than -1, the smallest integer greater than or equal to -1.59 is -1, so the function returns -1.

Example 3 – Using CEIL() in a Query

Let’s use a sample table products from a business database:

We can find the average MSRP per product line, rounded up to the next integer:

SELECT
    productLine,
    CEIL(AVG(msrp)) AS averageMsrp
FROM products
GROUP BY productLine
ORDER BY averageMsrp;

Explanation:

• AVG(msrp) calculates the average MSRP (which may be a decimal).
• CEIL() ensures we round up each average MSRP to the nearest integer.

Output Example:

7. Additional Examples

Example 4 – Using CEIL() with an Arithmetic Expression

Suppose you want to increase all prices by 7.5% and round up to the nearest integer:

SELECT
    product_id,
    price,
    CEIL(price * 1.075) AS new_price
FROM products;

Explanation:

• Multiplies each price by 1.075 (a 7.5% increase).
• CEIL() ensures the new price is rounded up to the next integer.

Example 5 – CEIL() with Division

You can use CEIL() to calculate the number of pages needed to display a dataset where each page shows 20 rows:

SELECT CEIL(COUNT(*) / 20.0) AS total_pages
FROM orders;

Explanation:

• If you have 95 records, 95 / 20 = 4.75.
• CEIL(4.75) → 5 pages are required.

Example 6 – CEIL() with JOIN or Subquery

Suppose you’re calculating the rounded-up average salary per department:

SELECT
    d.department_name,
    CEIL(AVG(e.salary)) AS rounded_avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

Explanation:

This query:

• Joins the employees and departments tables.
• Computes each department’s average salary.
• Rounds each average up to the next integer using CEIL().

8. Comparison Between CEIL() and ROUND()

9. Key Takeaways

• CEIL() is used for always rounding up a value.
• Use it in data reporting where you need to ensure no partial values (e.g., page counts, quantity calculations, or price adjustments).
• Works seamlessly with AVG(), SUM(), and COUNT() functions.
• Synonym: CEILING()
• Returns the smallest integer ≥ input value.

10. Practice Exercise

Try these queries to strengthen your understanding:

-- 1. Round up total price after tax
SELECT CEIL(125.78 * 1.2) AS total_after_tax;

-- 2. Round up each product discount to the next integer
SELECT product_id, CEIL(discount) FROM discounts;

-- 3. Determine how many storage boxes are needed
SELECT CEIL(total_items / 12.0) AS boxes_required FROM inventory;

-- 4. Compare CEIL() and FLOOR()
SELECT CEIL(5.1) AS ceil_value, FLOOR(5.1) AS floor_value;

✅ Summary

Use the MySQL CEIL() (or CEILING()) function to return the smallest integer greater than or equal to the input number.

It is especially useful when calculating totals, averages, or counts where you cannot have fractional results.