MySQL ABS Function

Summary: in this tutorial, you will learn how to use the MySQL ABS() function to return the absolute value of a number.

MySQL ABS() function overview

The ABS() function is a mathematical function that returns the absolute (no-negative) value of a numeric expression or a numeric column.

The ABS() function removes the sign of a number. So if the input is positive or negative, it always returns a positive number or zero.

Here’s the syntax of the ABS() function:

ABS(numeric_expression)Code language: SQL (Structured Query Language) (sql)

In this syntax, numeric_expression is a literal number or an expression that evaluates to a number.

If numeric_expression is a negative number, the ABS() function returns the positive value of the negative value. If numeric_expression is zero or positive, ABS() function has no effect.

The data type of the return value is the same as the data type of the input argument.

MySQL ABS() function examples

Let’s take some examples of using the ABS() function.

1) Simple ABS() function example

The following example uses the ABS function to return the absolute values of three different numbers:

SELECT 
    ABS(-10), 
    ABS(0), 
    ABS(10);  Code language: SQL (Structured Query Language) (sql)

Output:

In this example, the ABS() function returns the absolute value of -10, which is 10. It doesn’t have any effect on the 0 and 10.

2) Using ABS() function on table columns

We will use the products table from the sample database for the demonstration:

The following query uses the ABS() function to find the product code, MSRP, and the deviation of product MSRP from the average MSRP of the product within its product line.

SELECT 
    productName, 
    productLine,
    msrp,
    ABS(
        ROUND(
            msrp - AVG(msrp) OVER (
                PARTITION BY productLine
            )
        )
    ) deviation
FROM
    products
ORDER BY 
    productName;Code language: SQL (Structured Query Language) (sql)

Output:

How it works.

First, the AVG() window function returns the average MSRP of each product within its product line:

AVG(msrp) OVER ( PARTITION BY productLine )Code language: SQL (Structured Query Language) (sql)

Second, use the following formula to return the difference between MSRP and average product line MSRP:

msrp - AVG(msrp) OVER (PARTITION BY productLine)Code language: SQL (Structured Query Language) (sql)

Third, use the ROUND() function to round the deviation to zero decimals.

ROUND(
     msrp - AVG(msrp) OVER (
        PARTITION BY productLine
       )
)Code language: SQL (Structured Query Language) (sql)

Finally, use the ABS() function to return the absolute values of deviations. Note that due to certain products having an MSRP lower than the average MSRP, their deviations from the average value are negative.

Summary

• Use the ABS() function to get the absolute value of a number.