MySQL Numeric Functions

Numeric functions perform mathematical operations on numeric values.

SELECT ABS(-100);

Result:

100

Numeric functions can be used with numbers, expressions, and numeric table columns.

The ABS() function returns the absolute value of a number.

SELECT ABS(-25);

Result:

25

It removes the negative sign from a negative number.

The ROUND() function rounds a number to a specified number of decimal places.

SELECT ROUND(125.678, 2);

Result:

125.68

Another example:

SELECT ROUND(125.678);

Result:

126

The CEIL() function returns the smallest integer that is greater than or equal to the given number.

SELECT CEIL(12.3);

Result:

13

Example:

SELECT CEIL(12.9);

Result:

13

The FLOOR() function returns the largest integer that is less than or equal to the given number.

SELECT FLOOR(12.9);

Result:

12

For negative numbers:

SELECT FLOOR(-12.3);

Result:

-13

The TRUNCATE() function removes decimal digits without rounding the number.

SELECT TRUNCATE(125.678, 2);

Result:

125.67

Here, the number is reduced to two decimal places without rounding.

The MOD() function returns the remainder after division.

SELECT MOD(10, 3);

Result:

1

Another syntax is:

SELECT 10 % 3;

This also returns:

1

The POWER() function returns a number raised to a specified power.

SELECT POWER(2, 3);

Result:

8

Because 2 × 2 × 2 = 8.

The SQRT() function returns the square root of a number.

SELECT SQRT(64);

Result:

8

Another example:

SELECT SQRT(100);

Result:

10

The SIGN() function tells whether a number is negative, zero, or positive.

SELECT SIGN(-20);
SELECT SIGN(0);
SELECT SIGN(20);

Results:

-1
0
1

The RAND() function generates a random floating-point value between 0 and 1.

SELECT RAND();

The result changes when the function is evaluated.

You can also provide a seed:

SELECT RAND(10);

The RADIANS() function converts an angle from degrees to radians.

SELECT RADIANS(180);

The result is approximately:

3.14159265

This is useful in mathematical and trigonometric calculations.

The DEGREES() function converts an angle from radians to degrees.

SELECT DEGREES(PI());

Result:

180

The PI() function returns the value of π (pi).

SELECT PI();

The result is approximately:

3.141592653589793

The EXP() function returns the value of e raised to the given power.

SELECT EXP(1);

The result is approximately:

2.718281828

It is used in exponential calculations.

The LOG() function returns the natural logarithm when used with one argument.

SELECT LOG(10);

The result is approximately:

2.302585

You can also specify a base:

SELECT LOG(10, 1000);

Result:

3

The LOG10() function returns the base-10 logarithm.

SELECT LOG10(1000);

Result:

3

Because 10³ = 1000.

The GREATEST() function returns the largest value from a list of expressions.

SELECT GREATEST(10, 25, 15, 8);

Result:

25

It can compare multiple numeric values.

The LEAST() function returns the smallest value from a list of expressions.

SELECT LEAST(10, 25, 15, 8);

Result:

8

Numeric functions can be applied directly to numeric columns.

SELECT
    fee,
    ROUND(fee, 0) AS rounded_fee,
    CEIL(fee) AS ceiling_fee,
    FLOOR(fee) AS floor_fee
FROM students;

This allows you to perform calculations while retrieving data.

Numeric functions can be used to calculate percentages.

SELECT
    marks,
    total_marks,
    ROUND((marks / total_marks) * 100, 2) AS percentage
FROM results;

This calculates percentage up to two decimal places.

You can use numeric expressions and ROUND() to calculate discounts.

SELECT
    price,
    discount,
    ROUND(price - (price * discount / 100), 2) AS final_price
FROM products;

The query calculates the price after applying the discount.

The AVG() function calculates the average of numeric values.

SELECT AVG(marks)
FROM results;

You can round the result:

SELECT ROUND(AVG(marks), 2)
FROM results;

AVG() is an aggregate function and will be covered in more detail in the Aggregate Functions lesson.

MySQL allows arithmetic operations directly inside SELECT statements.

SELECT 10 + 5;
SELECT 10 - 5;
SELECT 10 * 5;
SELECT 10 / 5;

Results:

15
5
50
2

These expressions can also be combined with numeric functions.

ROUND() can be applied to the result of a mathematical expression.

SELECT ROUND((15000 * 18) / 100, 2) AS gst_amount;

This calculates 18% of 15000 and rounds the result to two decimal places.

Result:

2700.00

GREATEST() and LEAST() can compare values from multiple columns.

SELECT
    student_name,
    GREATEST(test1, test2, test3) AS highest_test,
    LEAST(test1, test2, test3) AS lowest_test
FROM results;

This can be useful when a student has multiple test scores.

Numeric expressions can be used in queries that filter records.

SELECT name, fee
FROM students
WHERE ROUND(fee, 0) >= 15000;

This selects students whose rounded fee is at least 15000.

Numeric functions can also be used for sorting results.

SELECT name, fee
FROM students
ORDER BY ROUND(fee, 0) DESC;

This sorts students by rounded fee from highest to lowest.

CREATE TABLE results (
    student_id INT,
    student_name VARCHAR(100),
    marks INT,
    total_marks INT,
    fee DECIMAL(10,2)
);

INSERT INTO results
(student_id, student_name, marks, total_marks, fee)
VALUES
(1, 'Amit Kumar', 425, 500, 15000.50),
(2, 'Priya Singh', 460, 500, 18000.75),
(3, 'Rahul Sharma', 390, 500, 12000.25);

-- Calculate percentage
SELECT
    student_name,
    ROUND((marks / total_marks) * 100, 2) AS percentage
FROM results;

-- Round fees
SELECT
    student_name,
    fee,
    ROUND(fee, 0) AS rounded_fee
FROM results;

-- Highest and lowest marks
SELECT
    GREATEST(marks, 0) AS highest_value,
    LEAST(marks, total_marks) AS lowest_value
FROM results;

-- Find average marks
SELECT ROUND(AVG(marks), 2) AS average_marks
FROM results;

-- Find remainder
SELECT MOD(marks, 10) AS remainder
FROM results;

This example demonstrates how numeric functions can be used for marks, fees, percentages, and calculations in a real database.