Numeric Functions

Introduction

Alongside text, most real applications also need to work with numbers — prices, quantities, averages, and scores. MySQL includes a full set of numeric functions so you can perform these calculations directly in your queries, instead of pulling raw numbers into an application first.

In this lesson we will use an "orders" table to explore ROUND(), CEIL(), FLOOR(), ABS(), MOD(), POWER(), and SQRT().

CREATE TABLE orders (
    id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2),
    quantity INT
);

INSERT INTO orders (id, product_name, price, quantity) VALUES
(1, 'Notebook', 4.756, 3),
(2, 'Backpack', 29.995, 1),
(3, 'Pen Set', 2.333, 10);

Why Numeric Functions Matter

Numeric functions are essential for tasks like displaying prices to two decimal places, calculating how many full boxes are needed to pack a given quantity, or working out statistics like an average score. Performing this math inside SQL keeps the calculation close to the data and avoids repeating logic in every application that reads it.

ROUND()

ROUND() rounds a number to a given number of decimal places. If you omit the second argument, it rounds to the nearest whole number.

SELECT price, ROUND(price, 2) AS rounded_price, ROUND(price) AS whole_price
FROM orders;

+--------+---------------+-------------+
| price  | rounded_price | whole_price |
+--------+---------------+-------------+
| 4.756  | 4.76          | 5           |
| 29.995 | 30.00         | 30          |
| 2.333  | 2.33          | 2           |
+--------+---------------+-------------+

CEIL() and FLOOR()

CEIL() (also written CEILING()) always rounds a number up to the nearest whole number, and FLOOR() always rounds it down, regardless of the decimal value.

SELECT price, CEIL(price) AS ceiling_value, FLOOR(price) AS floor_value
FROM orders;

+--------+---------------+-------------+
| price  | ceiling_value | floor_value |
+--------+---------------+-------------+
| 4.756  | 5             | 4           |
| 29.995 | 30            | 29          |
| 2.333  | 3             | 2           |
+--------+---------------+-------------+

A common real use is calculating how many boxes are needed to ship a quantity of items when each box holds a fixed amount — CEIL() ensures you always get a whole number of boxes, even for a partially filled last box.

ABS()

ABS() returns the absolute value of a number — its value with the sign removed, so negative numbers become positive.

SELECT ABS(-15) AS abs_negative, ABS(15) AS abs_positive;

+--------------+--------------+
| abs_negative | abs_positive |
+--------------+--------------+
| 15           | 15           |
+--------------+--------------+

This is useful for calculating a difference between two values where you only care about the size of the gap, not which value was larger — for example, comparing an expected quantity to an actual quantity delivered.

MOD() and the % Operator

MOD() returns the remainder after dividing one number by another. The % operator does the exact same thing and can be used interchangeably.

SELECT quantity, MOD(quantity, 3) AS mod_result, quantity % 3 AS percent_result
FROM orders;

+----------+------------+----------------+
| quantity | mod_result | percent_result |
+----------+------------+----------------+
| 3        | 0          | 0              |
| 1        | 1          | 1              |
| 10       | 1          | 1              |
+----------+------------+----------------+

POWER() and SQRT()

POWER() raises a number to a given exponent, and SQRT() returns the square root of a number.

SELECT POWER(2, 3) AS two_cubed, SQRT(81) AS square_root_of_81;

+------------+--------------------+
| two_cubed  | square_root_of_81  |
+------------+--------------------+
| 8          | 9                  |
+------------+--------------------+

Reference Table

Here is a quick reference for the numeric functions covered in this lesson.

Worked Example: Discounted Price

Let's calculate a 15% discounted price for every order, a rounded total cost, and check whether the quantity ordered is an even number.

SELECT
    product_name,
    price,
    ROUND(price * 0.85, 2) AS discounted_price,
    ROUND(price * quantity, 2) AS total_cost,
    CASE WHEN MOD(quantity, 2) = 0 THEN 'Even' ELSE 'Odd' END AS quantity_type
FROM orders;

+--------------+--------+-------------------+------------+---------------+
| product_name | price  | discounted_price  | total_cost | quantity_type |
+--------------+--------+-------------------+------------+---------------+
| Notebook     | 4.756  | 4.04              | 14.27      | Odd           |
| Backpack     | 29.995 | 25.50             | 30.00      | Odd           |
| Pen Set      | 2.333  | 1.98              | 23.33      | Even          |
+--------------+--------+-------------------+------------+---------------+

This query rounds the raw prices to a clean two-decimal discounted price, calculates a rounded total cost for the full quantity, and uses MOD() to label each order's quantity as even or odd (the CASE expression used here is covered in full in the next lesson).

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Numeric functions let you perform calculations like rounding, flooring, and remainder checks directly inside your SQL queries, which is especially useful for pricing, quantities, and statistics.

In this lesson, you learned ROUND(), CEIL(), FLOOR(), ABS(), MOD(), POWER(), and SQRT(), and used several of them together to calculate a discounted price. Next, you will explore MySQL's date and time functions.