# Three Handy MySQL Numerical Functions

Tuesday Sep 3rd 2013 by Rob Gravelle
Share:

One of the tenets of Third Normal Form (3NF) database normalization is that calculations should not be stored in a table. In MySQL, calculations are made easier by a number of built-in functions. In today's article, Rob Gravelle provides an overview of three of the more widely applicable ones.

One of the tenets of Third Normal Form (3NF) database normalization is that calculations should not be stored in a table. Usually, it's best to just perform the calculations while evaluating a query. In MySQL, all these calculations are made easier by a number of built-in functions. In today's article, I'll be giving an overview of three of the more widely applicable ones that you can utilize in your own queries.

## ABS

The ABS() function returns the absolute value - i.e. a positive number - specified in the argument. Absolute numbers are useful in the calculation of differences between distances or performance between objects of people. Hence, absolute numbers are fundamental to statistics, where they are used to quantify how well or how poorly one thing predicts or compares to another. These types of statistics are used in many important real world applications, including medicine and finance.

Here's a financial example, a user function that aids in determining how much funds to invest based on the percentage change in the market compared to a target deviation threshold. The ABS() function is applied to the percentChange, which can be positive or negative. A positive value is required in order to obtain the difference between it and the target deviation threshold.

USE   `finances`;
DROP function IF EXISTS `calcDiffBetweenTargetThresholdAndActualChange`;
DELIMITER   \$\$
USE `finances`
\$\$
CREATE FUNCTION calcDiffBetweenTargetThresholdAndActualChange(
fundValue     DECIMAL(8,2),
percentChange   DECIMAL(5,4),
deviationThreshold DECIMAL(5,4)) RETURNS decimal(8,2)
BEGIN
return strategyFundValue * (ABS(percentChange) - deviationThreshold);
END
\$\$
DELIMITER   ;

Here is the result of calling our function with a fund value of \$10,000.00, a market change of minus 0.0391 (or -3.91%), and a target deviation threshold of 0.02 (or 2%):

SELECT   calcDiffBetweenTargetThresholdAndActualChange(10000.00, -0.0391, 0.02)
AS   'Investment Amount';
+   --  --  --  --  --  --  --  --  -- -+
| Investment Amount |
+ --  --  --  --  --  --  --  --  -- -+
| 191.00      |
+ --  --  --  --  --  --  --  --  -- -+

## POWER / POW

POWER(), or it's abbreviated cousin POW(), returns the value of a number raised to the power of another number. The first argument is the base of the exponentiation, while the second is the exponent to apply. Hence, calling POWER(3,2) returns 3 to the power of 2, or three squared, equaling 9.

Some of the most common applications of exponential functions include the calculation of interest earned on an investment, population growth, and carbon dating.

Sticking with our finance theme, let's create a function to calculate annualized returns. The formula for that is:

((principal + gain)/principal) ^ (365/days) - 1

The caret symbol (^) above represents the exponent operator for raising a value to a power. To map the above equation to the POWER() function, the expression before the caret would be passed to the function as the first argument, whereas the "(365/days)" expression following the caret would be the second argument.

Here is the complete function:

USE   `finances`;
DROP function IF EXISTS `finances`.`calcAnnualizedReturns`;
DELIMITER   \$\$
USE `finances`
\$\$
CREATE FUNCTION `calcAnnualizedReturns`(
startPortfolioValue DECIMAL(8,2),
endPortfolioValue  DECIMAL(8,2),
days        SMALLINT) RETURNS decimal(8,2)
BEGIN
return POW(endPortfolioValue/startPortfolioValue, 365/days) - 1; END
\$\$
DELIMITER ;

Our formula tells us that an increase of \$2400 in 568 days equates to an annualized return of 15%:

SELECT   calcAnnualizedReturns(10000.00, 12400.00, 568) AS 'Annualized Returns';
+   --  --  --  --  --  --  --  --  --  -- +
| Annualized Returns |
+ --  --  --  --  --  --  --  --  --  -- +
| 0.15        |
+ --  --  --  --  --  --  --  --  --  -- +

Unfortunately, it also reports that a decrease of \$1800 in the same time period produces an annualized return of -12%:

SELECT   calcAnnualizedReturns(10000.00, 8200.00, 568) AS 'Annualized Returns';
+   --  --  --  --  --  --  --  --  --  -- +
| Annualized Returns |
+ --  --  --  --  --  --  --  --  --  -- +
| -0.12       |
+ --  --  --  --  --  --  --  --  --  -- +

### Negative Exponents

MySQL's POWER() function also supports negative exponents. A negative exponent divides the base number instead of multiplying. For example:

8-1 = 1 ÷ 8 = 1/8 or 0.125

5-3 = 1 ÷ 5 ÷ 5 ÷ 5 = 0.008

This saves us from having to handle negative exponents by first calculating the positive exponent (an) and then taking the reciprocal (i.e. 1/an).

Here is the above 5-3 exponent as evaluated by the POWER() function:

SELECT   POW(5, -3);
+   --  --  --  --  --  --  --  --  --  -- +
| POW(5, -3)     |
+ --  --  --  --  --  --  --  --  --  -- +
| 0.008       |
+ --  --  --  --  --  --  --  --  --  -- +

## ROUND

In calculations involving money, it is a common practice to use an exact value data type with high precision (i.e. a large number of digits following the decimal point) so as to preserve exact precision. Only at the very end of a calculation is the number of fractional digits reduced to two so as to represent cents. This is achieved through various types of rounding.

Rounding means reducing the digits in a number while trying to keep its value as close as possible. The resulting figure is less accurate, but easier to use. For example, 73 rounded to the nearest ten is 70, because 73 is closer to 70 than to 80.

The MySQL Round() function uses Half Away From ZERO rounding. That's similar to the kind that you learned back in grade school, but including negative numbers. Remember that 5 is rounded up because once you're half way there, you might as well continue in the same direction. In Half Away From ZERO rounding, negative numbers work in the same way, so that they tend to move away from zero, so that:

• 7.5 or 7.6 round up to 8
• 7.4 rounds down to 7
• -7.4 rounds to -7
• -7.5 or -7.6 round away from zero to -8

### Setting Rounding Precision

The Round() function also accepts a second parameter indicating up to how many decimal places the first number will be rounded.

SELECT   ROUND(9.955, 2);
+   --  --  --  --  --  --  --  --  --  -- +
| ROUND(9.955, 2)  |
+ --  --  --  --  --  --  --  --  --  -- +
| 9.96        |
+ --  --  --  --  --  --  --  --  --  -- +

### Rounding To Whole Numbers

It is also possible to round to the nearest tenth, hundredth, etc. by passing in a negative number as the second parameter. The following function call evaluates to twenty because the negative one parameter tells the function to round to the nearest tenth. Without the second argument, we would obtain a value of nineteen instead because 0.455 would round down to the nearest whole number rather than up:

SELECT   ROUND(19.455, -1);
+   --  --  --  --  --  --  --  --  --  -- +
| ROUND(19.455, -1) |
+ --  --  --  --  --  --  --  --  --  -- +
| 20         |
+ --  --  --  --  --  --  --  --  --  -- +

## Conclusion

Today we looked at three outstanding examples of built-in MySQL numeric functions. There are plenty more to discover. For a comprehensive listing, I urge you to visit the MySQL Development Site. There you'll find a list of functions and operators as well.

See all articles by Rob Gravelle

Share:
Home
Mobile Site | Full Site