MySQL Built-in Functions II — Mathematical Functions

Mathematical (numeric) functions work on numbers and return a number. They are handy for rounding money amounts, finding remainders, and other calculations inside a query. The common ones are:

• ROUND(n, d) — rounds the number n to d decimal places. ROUND(2567.89, 0) is 2568; ROUND(2567.856, 2) is 2567.86. If d is left out it rounds to a whole number.
• TRUNCATE(n, d) — cuts the number to d decimal places without rounding (it simply drops the extra digits). TRUNCATE(2567.89, 1) is 2567.8.
• MOD(a, b) — the remainder when a is divided by b (the same as the % operator). MOD(17, 5) is 2. It is often used to test whether a number is even or odd.
• POWER(a, b) (also POW) — a raised to the power b. POWER(2, 3) is 8.
• SQRT(n) — the square root of n. SQRT(81) is 9.
• ABS(n) — the absolute (positive) value of n. ABS(-50) is 50.
• CEIL(n) (also CEILING) — the smallest whole number not less than n (rounds up). CEIL(4.1) is 5.
• FLOOR(n) — the largest whole number not greater than n (rounds down). FLOOR(4.9) is 4.

These are used inside SELECT. For example, to show each salary rounded to whole rupees, and its square root (just as a demonstration):

SELECT Name, ROUND(Salary, 0) AS RoundedSalary
FROM Employee
WHERE Salary IS NOT NULL;

To give everyone a 10% bonus and display it rounded to two decimal places:

SELECT Name, ROUND(Salary * 0.10, 2) AS Bonus
FROM Employee
WHERE Salary IS NOT NULL;
``` …