Mathematical Functions - MySQL

As you might guess from the name, mathematical functions return the result of a mathematical operation and usually require a numeric expression as an input parameter, which can be a literal value, a numeric table column value, or any expression (including the output of another function) that yields a numeric value.

SIGN

The SIGN function takes in a numeric expression and returns one of the following values based on the sign of the input number:

Here is an example:

SELECT LATE_OR_LOSS_FEE,
       SIGN(LATE_OR_LOSS_FEE) AS FEE_SIGN
  FROM MOVIE_RENTAL
 WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE FEE_SIGN
---------------- --------
           29.99        1
               4        1
               4        1
           29.98        1

SQRT

The SQRT function takes in a single numeric expression and returns its square root. The general syntax is

SQRT (numeric_expression)

The result is a bit meaningless, but let’s take the square root of the non-null Late or Loss Fees we just looked at:

SELECT LATE_OR_LOSS_FEE,
       SQRT(LATE_OR_LOSS_FEE) AS FEE_SQRT
  FROM MOVIE_RENTAL
 WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE    FEE_SQRT
----------------  ----------
           29.99  5.47631263
               4           2
               4           2
           29.98  5.47539953

CEILING (CEIL)

The CEILING function returns the smallest integer that is greater than or equal to the value of the numeric expression provided as an input parameter. In other words, it rounds up to the next nearest whole number. There are some interesting naming compatibility issues across SQL implementations: Microsoft SQL Server uses the name CEILING, Oracle uses the name CEIL, and both DB2 and MySQL allow either name (CEIL or CEILING) to be used.

As an example, let’s apply CEILING to the Late or Loss Fees (if you are using Oracle, change CEILING to CEIL):

SELECT LATE_OR_LOSS_FEE,
       CEILING(LATE_OR_LOSS_FEE) AS FEE_CEILING
 FROM MOVIE_RENTAL
WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE  FEE_CEILING
----------------  -----------
            4.00            4
            4.00            4
           29.99           30
           29.98           30

FLOOR

The FLOOR function is the logical opposite of the CEILING function—it returns the integer that is less than or equal to the value of the numeric expression provided as an input parameter. In other words, it rounds down to the next nearest whole number.

Here is an example showing FLOOR applied to Late or Loss Fees:

SELECT LATE_OR_LOSS_FEE,
       FLOOR(LATE_OR_LOSS_FEE) AS FEE_FLOOR
  FROM MOVIE_RENTAL
 WHERE LATE_OR_LOSS_FEE IS NOT NULL;
LATE_OR_LOSS_FEE  FEE_FLOOR
----------------  ---------
            4.00          4
            4.00          4
           29.99         29
           29.98         29