Numeric Functions in SQL | Beginner’s Guide to Mathematical Operations
Introduction
SQL provides a variety of numeric functions that allow you to perform mathematical operations on numeric data. These functions help you calculate, round, and manipulate numbers directly within your database queries. In this section, you'll learn about the most commonly used numeric functions in SQL and how to apply them effectively.
1. ABS() – Absolute Value
The ABS() function returns the absolute value of a number, meaning it removes any negative sign and converts the number to positive.
Syntax:
ABS(number);
Example:
SELECT ABS(-25) AS absolute_value;
This returns 25 as the absolute value of -25.
2. ROUND() – Rounding Numbers
The ROUND() function is used to round a numeric value to a specified number of decimal places.
Syntax:
ROUND(number, decimal_places);
Example:
SELECT ROUND(15.6789, 2) AS rounded_value;
This rounds 15.6789 to 2 decimal places, returning 15.68.
3. CEIL() or CEILING() – Rounding Up
The CEIL() or CEILING() function returns the smallest integer greater than or equal to the given number. It rounds the number up.
Syntax:
CEIL(number);
-- or
CEILING(number);
Example:
SELECT CEILING(15.2) AS rounded_up_value;
This rounds 15.2 up to 16.
4. FLOOR() – Rounding Down
The FLOOR() function returns the largest integer less than or equal to the given number. It rounds the number down.
Syntax:
FLOOR(number);
Example:
SELECT FLOOR(15.8) AS rounded_down_value;
This rounds 15.8 down to 15.
5. MOD() – Modulo Operation
The MOD() function returns the remainder of the division of two numbers. This is also called the modulo operation.
Syntax:
MOD(number, divisor);
Example:
SELECT MOD(25, 4) AS remainder;
This returns the remainder of dividing 25 by 4, which is 1.
6. POW() or POWER() – Exponentiation
The POW() or POWER() function returns a number raised to the power of another number.
Syntax:
POW(base, exponent);
-- or
POWER(base, exponent);
Example:
SELECT POW(2, 3) AS power_value;
This calculates 2 raised to the power of 3, returning 8.
7. SQRT() – Square Root
The SQRT() function returns the square root of a number.
Syntax:
SQRT(number);
Example:
SELECT SQRT(16) AS square_root_value;
This returns the square root of 16, which is 4.
8. RANDOM() or RAND() – Generating Random Numbers
The RANDOM() (in some databases) or RAND() (in others) function generates a random floating-point number between 0 and 1.
Syntax:
RANDOM();
-- or
RAND();
Example:
SELECT RANDOM() AS random_value;
This returns a random number between 0 and 1.
9. PI() – Returning the Value of Pi
The PI() function returns the value of Pi (π), approximately 3.14159.
Syntax:
PI();
Example:
SELECT PI() AS pi_value;
This returns the value of Pi, 3.14159.
10. EXP() – Exponentiation (e^x)
The EXP() function returns the value of e raised to the power of x (where e is the base of the natural logarithm, approximately 2.71828).
Syntax:
EXP(number);
Example:
SELECT EXP(2) AS exponential_value;
This calculates e raised to the power of 2, returning approximately 7.38906.
Conclusion
SQL's numeric functions are incredibly useful for performing a variety of mathematical operations on your data. Whether you need to round numbers, calculate absolute values, perform exponentiation, or generate random numbers, these functions provide powerful tools for data manipulation directly within your SQL queries.