Numeric & Math Functions0%

Numeric & Math Functions

Beginner12 min read•Updated: 2026-09-12

Numeric & Math Functions: Rounding, Arithmetic, and Truncation

From calculating sales commissions and compound interest to processing GPS Euclidean distances and statistical distributions, SQL provides a comprehensive suite of mathematical functions. In this tutorial, you will master the most common scalar mathematical operations in MySQL.

1. Rounding and Precision: `ROUND()` vs `TRUNCATE()`

The two most common methods for reducing decimal precision are rounding and truncation:

`ROUND(number, [decimals])`

Rounds a number according to standard mathematical rounding rules (0.5 and above rounds up, below rounds down):

1SELECT ROUND(125.756, 2); -- Returns 125.76
2SELECT ROUND(125.754, 2); -- Returns 125.75
3SELECT ROUND(125.75);    -- Returns 126 (Default is 0 decimal places)
4SELECT ROUND(125.75, -1); -- Returns 130 (Negative rounds to nearest tens place!)

`TRUNCATE(number, decimals)`

Cuts off decimal places immediately without rounding:

1SELECT TRUNCATE(125.756, 2); -- Returns 125.75 (Does not round up!)
2SELECT TRUNCATE(125.756, 0); -- Returns 125

2. Integer Boundary Functions: `CEIL()` and `FLOOR()`

CEIL() / CEILING(): Rounds a decimal up to the nearest integer.
FLOOR(): Rounds a decimal down to the nearest integer.

1SELECT CEIL(4.01);   -- Returns 5
2SELECT CEIL(-4.99);  -- Returns -4
3 
4SELECT FLOOR(4.99);  -- Returns 4
5SELECT FLOOR(-4.01); -- Returns -5

Real-World Use Case: Pagination Page Count

1-- If total_records = 95 and page_size = 10, how many total pages are needed?
2SELECT CEIL(95 / 10.0) AS total_pages; -- Returns 10 pages!

3. Absolute Values & Modulo Arithmetic

`ABS(number)`

Returns the non-negative absolute magnitude of a number:

1SELECT ABS(-45.50); -- Returns 45.50

`MOD(N, M)` or `N % M` (Remainder)

Returns the remainder of dividing N by M:

1SELECT MOD(17, 5); -- Returns 2 (17 / 5 = 3 with remainder 2)
2 
3-- Check for even or odd row numbers:
4SELECT order_id FROM orders WHERE MOD(order_id, 2) = 0; -- Even orders

4. Exponential and Logarithmic Functions

1-- Power: 2 raised to the power of 8 (2^8)
2SELECT POWER(2, 8); -- Returns 256
3 
4-- Square Root of 144
5SELECT SQRT(144); -- Returns 12
6 
7-- Random Floating-Point Number between 0.0 and 1.0
8SELECT RAND();
9 
10-- Random Integer between 1 and 100:
11SELECT FLOOR(1 + RAND() * 100) AS random_pin;

5. Best Practices & Common Pitfalls

Avoid Division by Zero: In MySQL, dividing any number by zero (100 / 0) returns NULL with a warning, rather than crashing the query. To handle potential zeros gracefully, use NULLIF:

1  -- Safe division: If total_orders is 0, NULLIF turns it into NULL, returning NULL safely:
2  SELECT revenue / NULLIF(total_orders, 0) AS average_order_value FROM daily_stats;

Floating-Point Imprecision: Remember that ROUND() on a FLOAT column can yield unexpected tiny fractional tails. Always cast or store monetary calculations in DECIMAL.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the output of SELECT ROUND(84.346, 2);?

Question 2

How does TRUNCATE(84.346, 2) differ from ROUND(84.346, 2)?

Question 3

Which function rounds a decimal value up to the next highest integer (e.g., turning 7.1 into 8)?

Question 4

What is the result of dividing a number by zero (e.g., SELECT 50 / 0;) in MySQL?

Question 5

What does SELECT MOD(25, 4); return?

Next Lesson

Date & Time Functions

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SQL String Functions	Date & Time Functions

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Date & Time Functions

Numeric & Math Functions

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Numeric & Math Functions: Rounding, Arithmetic, and Truncation

1. Rounding and Precision: `ROUND()` vs `TRUNCATE()`

The two most common methods for reducing decimal precision are rounding and truncation:

`ROUND(number, [decimals])`

Rounds a number according to standard mathematical rounding rules (0.5 and above rounds up, below rounds down):

1SELECT ROUND(125.756, 2); -- Returns 125.76
2SELECT ROUND(125.754, 2); -- Returns 125.75
3SELECT ROUND(125.75);    -- Returns 126 (Default is 0 decimal places)
4SELECT ROUND(125.75, -1); -- Returns 130 (Negative rounds to nearest tens place!)

`TRUNCATE(number, decimals)`

Cuts off decimal places immediately without rounding:

1SELECT TRUNCATE(125.756, 2); -- Returns 125.75 (Does not round up!)
2SELECT TRUNCATE(125.756, 0); -- Returns 125

2. Integer Boundary Functions: `CEIL()` and `FLOOR()`

CEIL() / CEILING(): Rounds a decimal up to the nearest integer.
FLOOR(): Rounds a decimal down to the nearest integer.

1SELECT CEIL(4.01);   -- Returns 5
2SELECT CEIL(-4.99);  -- Returns -4
3 
4SELECT FLOOR(4.99);  -- Returns 4
5SELECT FLOOR(-4.01); -- Returns -5

Real-World Use Case: Pagination Page Count

1-- If total_records = 95 and page_size = 10, how many total pages are needed?
2SELECT CEIL(95 / 10.0) AS total_pages; -- Returns 10 pages!

3. Absolute Values & Modulo Arithmetic

`ABS(number)`

Returns the non-negative absolute magnitude of a number:

1SELECT ABS(-45.50); -- Returns 45.50

`MOD(N, M)` or `N % M` (Remainder)

Returns the remainder of dividing N by M:

1SELECT MOD(17, 5); -- Returns 2 (17 / 5 = 3 with remainder 2)
2 
3-- Check for even or odd row numbers:
4SELECT order_id FROM orders WHERE MOD(order_id, 2) = 0; -- Even orders

4. Exponential and Logarithmic Functions

1-- Power: 2 raised to the power of 8 (2^8)
2SELECT POWER(2, 8); -- Returns 256
3 
4-- Square Root of 144
5SELECT SQRT(144); -- Returns 12
6 
7-- Random Floating-Point Number between 0.0 and 1.0
8SELECT RAND();
9 
10-- Random Integer between 1 and 100:
11SELECT FLOOR(1 + RAND() * 100) AS random_pin;

5. Best Practices & Common Pitfalls

Avoid Division by Zero: In MySQL, dividing any number by zero (100 / 0) returns NULL with a warning, rather than crashing the query. To handle potential zeros gracefully, use NULLIF:

1  -- Safe division: If total_orders is 0, NULLIF turns it into NULL, returning NULL safely:
2  SELECT revenue / NULLIF(total_orders, 0) AS average_order_value FROM daily_stats;

Floating-Point Imprecision: Remember that ROUND() on a FLOAT column can yield unexpected tiny fractional tails. Always cast or store monetary calculations in DECIMAL.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the output of SELECT ROUND(84.346, 2);?

Question 2

How does TRUNCATE(84.346, 2) differ from ROUND(84.346, 2)?

Question 3

Which function rounds a decimal value up to the next highest integer (e.g., turning 7.1 into 8)?

Question 4

What is the result of dividing a number by zero (e.g., SELECT 50 / 0;) in MySQL?

Question 5

What does SELECT MOD(25, 4); return?

Next Lesson

Date & Time Functions

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
SQL String Functions	Date & Time Functions

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Numeric & Math Functions: Rounding, Arithmetic, and Truncation

1. Rounding and Precision: ROUND() vs TRUNCATE()

ROUND(number, [decimals])

TRUNCATE(number, decimals)

2. Integer Boundary Functions: CEIL() and FLOOR()

Real-World Use Case: Pagination Page Count

3. Absolute Values & Modulo Arithmetic

ABS(number)

MOD(N, M) or N % M (Remainder)

4. Exponential and Logarithmic Functions

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Date & Time Functions

Related Lessons

Practice Quiz

Loading Page Content...

Numeric & Math Functions: Rounding, Arithmetic, and Truncation

1. Rounding and Precision: ROUND() vs TRUNCATE()

ROUND(number, [decimals])

TRUNCATE(number, decimals)

2. Integer Boundary Functions: CEIL() and FLOOR()

Real-World Use Case: Pagination Page Count

3. Absolute Values & Modulo Arithmetic

ABS(number)

MOD(N, M) or N % M (Remainder)

4. Exponential and Logarithmic Functions

5. Best Practices & Common Pitfalls

Multiple Choice Questions

Date & Time Functions

Related Lessons

Practice Quiz

1. Rounding and Precision: `ROUND()` vs `TRUNCATE()`

`ROUND(number, [decimals])`

`TRUNCATE(number, decimals)`

2. Integer Boundary Functions: `CEIL()` and `FLOOR()`

`ABS(number)`

`MOD(N, M)` or `N % M` (Remainder)

1. Rounding and Precision: `ROUND()` vs `TRUNCATE()`

`ROUND(number, [decimals])`

`TRUNCATE(number, decimals)`

2. Integer Boundary Functions: `CEIL()` and `FLOOR()`

`ABS(number)`

`MOD(N, M)` or `N % M` (Remainder)