MySQL

In MySQL, numeric functions allow you to perform various mathematical operations on numeric data. These functions can be used for calculations such as rounding, generating absolute values, finding minimums or maximums, and more. Numeric functions are vital when working with financial data, performing statistical analyses, or generating reports.

Here’s a detailed guide on commonly used numeric functions in MySQL, along with examples:

1. ABS():

   • The ABS() function returns the absolute value of a number.
   • Syntax:

   SELECT ABS(salary - 50000) AS salary_difference 
   FROM employees;

   • This query calculates the absolute difference between each employee’s salary and 50,000.

2. CEIL() or CEILING():

   • The CEIL() or CEILING() function rounds a number up to the nearest integer.
   • Example:

   SELECT CEIL(salary / 1000) AS rounded_up_salary 
   FROM employees;

   • This query rounds up each employee’s salary to the nearest thousand.

3. FLOOR():

   • The FLOOR() function rounds a number down to the nearest integer.
   • Example:

   SELECT FLOOR(salary / 1000) AS rounded_down_salary 
   FROM employees;

   • This query rounds down each employee’s salary to the nearest thousand.

4. ROUND():

   • The ROUND() function rounds a number to a specified number of decimal places.
   • Example:

   SELECT ROUND(salary, 2) AS rounded_salary 
   FROM employees;

   • This query rounds each employee’s salary to two decimal places.

5. MOD():

   • The MOD() function returns the remainder of a division operation.
   • Example:

   SELECT MOD(salary, 1000) AS remainder_salary 
   FROM employees;

   • This query returns the remainder when each employee’s salary is divided by 1,000.

6. POWER():

   • The POWER() function returns a number raised to a specified power.
   • Example:

   SELECT POWER(salary, 2) AS salary_squared 
   FROM employees;

   • This query returns the salary of each employee squared.

7. SQRT():

   • The SQRT() function returns the square root of a number.
   • Example:

   SELECT SQRT(salary) AS salary_sqrt 
   FROM employees;

   • This query returns the square root of each employee’s salary.

8. EXP():

   • The EXP() function returns the exponential value of a number (e raised to the power of the number).
   • Example:

   SELECT EXP(salary / 10000) AS exp_salary 
   FROM employees;

   • This query calculates the exponential value of the salary divided by 10,000 for each employee.

9. LOG():

   • The LOG() function returns the natural logarithm of a number.
   • Example:

   SELECT LOG(salary) AS log_salary 
   FROM employees;

   • This query returns the natural logarithm of each employee’s salary.

10. GREATEST() and LEAST():

    • The GREATEST() function returns the largest value among the given inputs, while LEAST() returns the smallest.
    • Example:

    SELECT GREATEST(salary, 50000) AS higher_salary, LEAST(salary, 50000) AS lower_salary 
    FROM employees;

    • This query compares each employee’s salary with 50,000, returning the higher and lower of the two values.

11. TRUNCATE():

    • The TRUNCATE() function truncates a number to a specified number of decimal places without rounding.
    • Example:

    SELECT TRUNCATE(salary, 2) AS truncated_salary 
    FROM employees;

    • This query truncates each employee’s salary to two decimal places.

12. SIGN():

    • The SIGN() function returns the sign of a number (-1, 0, or 1).
    • Example:

    SELECT SIGN(salary - 50000) AS salary_sign 
    FROM employees;

    • This query returns -1 if the salary is less than 50,000, 1 if it’s greater, and 0 if it’s equal.

Numeric functions in MySQL provide a powerful way to manipulate numerical data in your queries, enabling you to perform advanced calculations and generate precise reports. These functions are especially useful in situations where mathematical operations are required for financial data, statistical analyses, or other numeric-based datasets.

To gain complete access, login with gmail or outlook, no need of signup, click here