MySQL Numeric Functions

MySQL provides built-in numeric functions to perform mathematical operations on number values stored in columns or used directly in queries. These functions cover rounding, absolute values, power, square root, modulus, and more.

Commonly Used Numeric Functions

ROUND()

SELECT ROUND(4.567, 2);   -- 4.57
SELECT ROUND(4.567, 0);   -- 5
SELECT ROUND(4.567, -1);  -- 0 (rounds to nearest 10)

CEIL() and FLOOR()

SELECT CEIL(4.1);   -- 5
SELECT CEIL(4.9);   -- 5
SELECT FLOOR(4.1);  -- 4
SELECT FLOOR(4.9);  -- 4

CEIL() always rounds up; FLOOR() always rounds down.

ABS()

SELECT ABS(-250);   -- 250
SELECT ABS(250);    -- 250

Returns the positive magnitude of any number.

MOD()

SELECT MOD(10, 3);  -- 1  (10 divided by 3 gives remainder 1)
SELECT MOD(20, 4);  -- 0  (20 is exactly divisible by 4)

POWER() / POW()

SELECT POWER(2, 10);  -- 1024
SELECT POW(3, 3);     -- 27

SQRT()

SELECT SQRT(144);  -- 12
SELECT SQRT(2);    -- 1.4142135623731

TRUNCATE()

SELECT TRUNCATE(4.789, 2);   -- 4.78  (no rounding)
SELECT TRUNCATE(4.789, 0);   -- 4
SELECT TRUNCATE(145.6, -2);  -- 100

Unlike ROUND(), TRUNCATE() simply removes digits without rounding.

RAND()

SELECT RAND();          -- 0.7382910934 (random, changes each run)
SELECT FLOOR(RAND() * 100);  -- Random integer between 0 and 99

SIGN()

SELECT SIGN(-50);  -- -1
SELECT SIGN(0);    --  0
SELECT SIGN(75);   --  1

Practical Example: Invoice Calculation

CREATE TABLE invoices (
    invoice_id INT PRIMARY KEY,
    item_name VARCHAR(100),
    unit_price DECIMAL(8,2),
    quantity INT,
    discount DECIMAL(5,2)
);

INSERT INTO invoices VALUES (1, 'Laptop Bag', 850.75, 3, 5.5);
INSERT INTO invoices VALUES (2, 'USB Cable', 199.99, 10, 0);

SELECT item_name,
       unit_price * quantity AS gross_total,
       ROUND(unit_price * quantity * (1 - discount / 100), 2) AS net_total
FROM invoices;

Result:

item_name  | gross_total | net_total
-----------+-------------+-----------
Laptop Bag | 2552.25     | 2412.48
USB Cable  | 1999.90     | 1999.90

Key Points

• ROUND() rounds a number to a specified number of decimal places.
• CEIL() rounds up; FLOOR() rounds down to the nearest integer.
• TRUNCATE() removes decimal digits without rounding.
• ABS() returns the positive value of a number.
• MOD() returns the remainder from division — useful for even/odd checks.