MySQL Numeric Functions

Numeric Functions

Numeric functions transform one or more numeric expressions for calculation or display. They are scalar functions: normally one result is produced for each input row.

SELECT full_name, marks,
       ROUND(marks / 100 * 5, 2) AS score_out_of_5
FROM students
ORDER BY student_id;

ROUND, CEIL, FLOOR and TRUNCATE

SELECT ROUND(88.50, 0) AS rounded,
       CEIL(88.50) AS ceiling_value,
       FLOOR(88.50) AS floor_value,
       TRUNCATE(88.56, 1) AS truncated;

ROUND and TRUNCATE are different. Also test negative numbers: CEIL(-2.7) is -2, while FLOOR(-2.7) is -3.

ABS, MOD, POW and SQRT

SELECT ABS(-12.50) AS absolute_value,
       MOD(91, 10) AS remainder,
       POW(2, 5) AS power_value,
       SQRT(81) AS square_root;

Validate domains: SQRT of a negative real value returns NULL in MySQL, and division/modulo by zero must be handled according to SQL mode and warnings.

Numeric Functions in Reports

SELECT full_name,
       marks,
       CASE
         WHEN marks >= 90 THEN 'A1'
         WHEN marks >= 80 THEN 'A2'
         ELSE 'B or below'
       END AS grade,
       ROUND(marks, 0) AS display_marks
FROM students
ORDER BY marks DESC;

Keep raw values for calculations and round only the display unless official assessment rules require stored rounding.

Precision, NULL and Safe Calculations

• Use DECIMAL for exact marks, fees and money.
• FLOAT/DOUBLE are approximate.
• Guard a denominator with NULLIF(denominator,0) when zero should produce NULL rather than invalid division.
• Most functions propagate NULL.
• Do not wrap indexed columns in functions inside WHERE without checking EXPLAIN and design alternatives.

SELECT obtained_marks / NULLIF(max_marks, 0) * 100
       AS percentage
FROM results;

Practice

1. Round each mark to one decimal.
2. Compare CEIL and FLOOR for -2.7.
3. Find the remainder when 86 is divided by 5.
4. Convert percentage to a five-point score.
5. Write division protected by NULLIF.

Quick Summary

• Rounding functions have different mathematical meanings.
• ABS, MOD, POW and SQRT perform common calculations.
• DECIMAL preserves exact fixed-point values.
• Handle NULL, zero denominators, domains and index use deliberately.

Official References

• Mathematical Functions — MySQL 8.4
• Precision Math
• Rounding Behavior

References reviewed 14 August 2026.