MySQL Mathematical Functions

Common MySQL Mathematical Functions

MySQL provides several built-in functions to perform mathematical operations on numeric data.

These functions include absolute value, rounding, ceiling, floor, and more.

• ABS(x): Returns the absolute value of x.
• CEIL(x) or CEILING(x): Returns the smallest integer greater than or equal to x.
• FLOOR(x): Returns the largest integer less than or equal to x.
• ROUND(x, d): Rounds x to d decimal places; if d is omitted, rounds to nearest integer.
• POW(x, y) or POWER(x, y): Returns x raised to the power y.
• SQRT(x): Returns the square root of x.

Using Mathematical Functions in Queries

You can use mathematical functions in SELECT statements, WHERE clauses, and even in UPDATE statements.

This allows you to manipulate data dynamically during query execution.

• Calculate absolute values to filter data: WHERE ABS(balance) > 100
• Round prices for display: SELECT ROUND(price, 2) FROM products
• Use CEIL or FLOOR to group numeric ranges

Examples of Mathematical Functions

Let's look at some practical examples demonstrating these functions.