Numerical Functions in SQL

Numerical Functions in SQL

Numerical functions are used to perform mathematical calculations and transformations on numeric data. They are helpful for tasks ranging from simple arithmetic to complex statistical calculations.

ABS

• ABS(number): Returns the absolute value of a number, which is the number without its sign.

Example 

SELECT ABS(-25) AS absolute_value;

Returns 25, the absolute value of -25.

ROUND

• ROUND(number, decimal_places): Rounds a number to the specified number of decimal places. If decimal_places is omitted, it defaults to 0, which rounds to the nearest integer.

Example 

SELECT ROUND(123.4567, 2) AS rounded_value;

Returns 123.46, which is the number 123.4567 rounded to two decimal places.

CEIL / CEILING

• CEIL(number) or CEILING(number): Rounds a number up to the nearest integer.

Example 

SELECT CEIL(7.1) AS ceiling_value;

Returns 8, the smallest integer greater than or equal to 7.1.

FLOOR

• FLOOR(number): Rounds a number down to the nearest integer.

Example 

SELECT FLOOR(7.9) AS floor_value;

Returns 7, the largest integer less than or equal to 7.9.

POWER

• POWER(number, power): Raises a number to the power of another number.

Example 

SELECT POWER(2, 3) AS power_value;

Returns 8, which is 2 raised to the power of 3.

SQRT

• SQRT(number): Returns the square root of a number.

Example 

SELECT SQRT(16) AS square_root;

Returns 4, the square root of 16.

EXP

• EXP(number): Returns the exponential value of e raised to the power of the given number.

Example 

SELECT EXP(1) AS exp_value;

Returns approximately 2.7183, which is e raised to the power of 1.

LOG

• LOG(number): Returns the natural logarithm of a number.
• LOG(base, number): Returns the logarithm of number to the specified base.

Example 

SELECT LOG(10) AS natural_log,
       LOG(10, 100) AS log_base_10;

• LOG(10) returns the natural logarithm of 10.
• LOG(10, 100) returns 2, because 10^2 equals 100.

MOD

• MOD(number, divisor): Returns the remainder of dividing number by divisor.

Example 

SELECT MOD(10, 3) AS remainder;

Returns 1, the remainder when 10 is divided by 3.

RADIANS and DEGREES

• RADIANS(degrees): Converts degrees to radians.
• DEGREES(radians): Converts radians to degrees.

Example 

SELECT RADIANS(180) AS radians_value,
       DEGREES(3.14159) AS degrees_value;

• RADIANS(180) returns approximately 3.14159, the radians equivalent of 180 degrees.
• DEGREES(3.14159) returns 180, the degrees equivalent of the given radians.

AVG

• AVG(expression): Calculates the average value of a numeric expression across a set of values.

Example 

SELECT AVG(salary) AS average_salary
FROM employees;

Returns the average salary of all employees.

SUM

• SUM(expression): Calculates the total sum of a numeric expression across a set of values.

Example 

SELECT SUM(sales) AS total_sales
FROM orders;

Returns the total sales amount from the orders table.

MIN and MAX

• MIN(expression): Returns the minimum value of a numeric expression.
• MAX(expression): Returns the maximum value of a numeric expression.

Example 

SELECT MIN(age) AS youngest_age,
       MAX(age) AS oldest_age
FROM users;

Returns the youngest and oldest ages from the users table.

VARIANCE and STDDEV

• VARIANCE(expression): Calculates the variance of a numeric expression.
• STDDEV(expression): Calculates the standard deviation of a numeric expression.

Example 

SELECT VARIANCE(salary) AS salary_variance,
       STDDEV(salary) AS salary_stddev
FROM employees;

Returns the variance and standard deviation of employee salaries.

Summary

Numerical functions in SQL are essential for performing mathematical operations and aggregations on numeric data. Whether you need to perform basic arithmetic, complex statistical calculations, or transformations, these functions provide the necessary tools to handle numerical data effectively. Understanding these functions and their usage will enhance your ability to analyze and manipulate data in your SQL queries.