Numeric Functions in SQL

Numeric functions perform mathematical operations on numeric values.

They can be used to:

• Round numbers
• Find absolute values
• Calculate powers
• Find remainders
• Generate random numbers
• Calculate square roots
• Perform mathematical calculations

Some commonly used numeric functions are:

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

SELECT ROUND(125.678, 2);

Result:

125.68

You can use ROUND() without specifying the number of decimal places.

SELECT ROUND(125.678);

Result:

126

ROUND() can be used with numeric columns.

SELECT
    name,
    ROUND(fee, 2) AS rounded_fee
FROM students;

This displays the fee rounded to two decimal places.

In MySQL, CEIL() returns the smallest integer greater than or equal to the given number.

SELECT CEIL(12.3);

Result:

13

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

SELECT FLOOR(12.9);

Result:

12

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

SELECT ABS(-25);

Result:

25

ABS() removes the negative sign from a negative number.

ABS() can be useful when calculating differences or values that should be treated as positive.

SELECT
    student_id,
    ABS(paid_fee - total_fee) AS fee_difference
FROM student_fees;

This returns the absolute difference between paid and total fees.

The MOD() function returns the remainder after division.

SELECT MOD(10, 3);

Result:

1

10 divided by 3 leaves a remainder of 1.

MOD() can be used to determine whether a number is even or odd.

SELECT MOD(10, 2);

A result of 0 means the number is even.

SELECT MOD(11, 2);

A result of 1 means the number is odd.

The POWER() function calculates one number raised to the power of another.

SELECT POWER(2, 3);

Result:

8

Because 2 × 2 × 2 = 8.

In MySQL, POW() can also be used to calculate powers.

SELECT POW(5, 2);

Result:

25

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

SELECT SQRT(64);

Result:

8

In MySQL, SIGN() returns the sign of a number.

SELECT SIGN(-10);

Result:

-1

SELECT SIGN(10);

Result:

1

SELECT SIGN(0);

Result:

0

In MySQL, RAND() generates a pseudo-random floating-point value between 0 and 1.

SELECT RAND();

Each execution can produce a different value.

RAND() can receive a seed value.

SELECT RAND(10);

Using the same seed can produce a repeatable sequence in MySQL.

In MySQL, the TRUNCATE() function cuts a number to a specified number of decimal places without rounding.

SELECT TRUNCATE(125.678, 2);

Result:

125.67

This is different from ROUND(), which would round the value.

In MySQL, GREATEST() returns the largest value from the supplied arguments.

SELECT GREATEST(10, 25, 15);

Result:

25

In MySQL, LEAST() returns the smallest value from the supplied arguments.

SELECT LEAST(10, 25, 15);

Result:

10

Numeric functions can be used in filtering conditions.

SELECT *
FROM students
WHERE ROUND(fee, 0) > 5000;

This filters records based on the rounded fee value.

Numeric functions can also be used when sorting results.

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

This sorts students according to the rounded fee.

Numeric functions can be combined with aggregate functions.

SELECT ROUND(AVG(marks), 2) AS average_marks
FROM students;

This calculates the average marks and rounds the result to two decimal places.

Numeric expressions can be used to calculate percentages.

SELECT
    name,
    marks,
    ROUND((marks / 100) * 100, 2) AS percentage
FROM students;

If the maximum marks are 100, the marks themselves represent the percentage.

Numeric calculations are useful for calculating discounts.

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

This calculates the final price after applying the discount percentage.

Numeric expressions can be used to calculate remaining fees.

SELECT
    student_id,
    total_fee,
    paid_fee,
    ROUND(total_fee - paid_fee, 2) AS remaining_fee
FROM student_fees;

This calculates the amount that is still due.

A common mistake is confusing rounding with truncation.

SELECT ROUND(12.678, 2);

Result: 12.68

SELECT TRUNCATE(12.678, 2);

Result: 12.67 in MySQL.

ROUND() changes the value according to rounding rules, while TRUNCATE() cuts off extra decimal places.

When a numeric function receives NULL, many SQL functions return NULL for that expression.

SELECT ROUND(NULL, 2);

The result is NULL.

When NULL values need a replacement value, functions such as COALESCE() can be useful.

Numeric functions are useful for creating student fee and marks reports.

SELECT
    name,
    marks,
    ROUND(marks, 2) AS rounded_marks,
    fee,
    ROUND(fee, 2) AS rounded_fee
FROM students;

This displays rounded marks and fee values.

Here is a complete example using several numeric functions.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    marks DECIMAL(5,2),
    total_fee DECIMAL(10,2),
    paid_fee DECIMAL(10,2)
);

SELECT
    name,
    ROUND(marks, 1) AS rounded_marks,
    ABS(total_fee - paid_fee) AS fee_difference,
    ROUND(total_fee - paid_fee, 2) AS remaining_fee
FROM students;

This query rounds marks, calculates the absolute fee difference, and calculates the remaining fee for each student.