Numeric Functions in MySQL

Numeric functions allow you to perform mathematical operations on column values directly in SQL. This eliminates the need to pull raw numbers into application code just to round or calculate them.

Key Numeric Functions

ROUND(n, d): Rounds n to d decimal places
CEIL(n) / FLOOR(n): Rounds up or down to the nearest integer
ABS(n): Absolute value
MOD(n, m): Remainder of n divided by m
POWER(n, e): Raises n to the power e
SQRT(n): Square root
TRUNCATE(n, d): Truncates (does not round) to d decimals

Financial Calculations

ROUND is particularly important in financial applications where currency values must be presented to exactly two decimal places. Using TRUNCATE instead of ROUND can lead to small systematic errors in totals.

Example

SELECT
    product_name,
    price,
    ROUND(price * 1.20, 2) AS price_with_vat,
    FLOOR(price) AS price_floor,
    CEIL(price) AS price_ceil,
    ABS(stock_delta) AS stock_change
FROM products;

SQL

Full Editor

Pro Tip

Use ROUND() for displaying currency values and TRUNCATE() when you need to cut decimals without rounding.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned