How the CEILING() function works in Mariadb?

The CEILING() function is a mathematical function that returns the smallest integer value that is greater than or equal to a given numeric value.

Posted on

The CEILING() function is a mathematical function that returns the smallest integer value that is greater than or equal to a given numeric value. The CEILING() function is equivalent to the CEIL() function, and can be used to round up a value to the nearest integer, or to get the ceiling of a value.

Syntax

The syntax of the CEILING() function is as follows:

CEILING(value)

Where:

  • value is a numeric expression that returns a value to be rounded up.

The return type of the function is an integer value.

Examples

Example 1: Rounding up a decimal value

In this example, we use the CEILING() function to round up a decimal value to the nearest integer. We use the SELECT statement to display the result.

SELECT CEILING(3.14) AS ceiling;

The output is:

+---------+
| ceiling |
+---------+
| 4       |
+---------+

Example 2: Rounding up a negative value

In this example, we use the CEILING() function to round up a negative value to the nearest integer. We use the SELECT statement to display the result.

SELECT CEILING(-3.14) AS ceiling;

The output is:

+---------+
| ceiling |
+---------+
| -3      |
+---------+

Example 3: Rounding up a column value

In this example, we use the CEILING() function to round up a column value to the nearest integer. We use the products table as an example, which has the following structure and data:

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Laptop', 999.99),
(2, 'Mouse', 19.99),
(3, 'Keyboard', 49.99),
(4, 'Monitor', 199.99),
(5, 'Speaker', 29.99);

We use the SELECT statement to display the product name and the rounded up price.

SELECT name, CEILING(price) AS rounded_price
FROM products;

The output is:

+---------+--------------+
| name    | rounded_price|
+---------+--------------+
| Laptop  | 1000         |
| Mouse   | 20           |
| Keyboard| 50           |
| Monitor | 200          |
| Speaker | 30           |
+---------+--------------+

Some other functions that are related to the CEILING() function are:

  • FLOOR(): Returns the largest integer value that is less than or equal to a given numeric value. The syntax is FLOOR(value).
  • ROUND(): Returns a numeric value rounded to a specified number of decimal places. The syntax is ROUND(value, decimals).
  • TRUNCATE(): Returns a numeric value truncated to a specified number of decimal places. The syntax is TRUNCATE(value, decimals).

For example, you can use the FLOOR() function to achieve the opposite result of the CEILING() function:

SELECT FLOOR(3.14) AS floor;

The output is:

+-------+
| floor |
+-------+
| 3     |
+-------+

Conclusion

The CEILING() function is a useful function to round up a numeric value to the nearest integer, or to get the ceiling of a value. The CEILING() function is equivalent to the CEIL() function, and takes a numeric expression as an argument, and returns an integer value that is greater than or equal to the argument. The CEILING() function can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses. The CEILING() function can be combined with other functions to perform various calculations and operations.