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 (positive) value of a number.

The following shows the syntax of the `ABS()` function:

`ABS(n) `

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

In this syntax, n is a literal number or an expression that evaluates to a number. If n is a negative number, the `ABS()` function changes the negative value to positive value. In case n 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.

The following graph illustrates the `ABS()` function:

MySQL `ABS()` function examples

The following example shows the results of using the `ABS()` function on four different numbers:

`SELECT ABS(-10), ABS(0), ABS(10); `

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

Here is the query output:

Using ABS function on table columns

We will use the `products` table for the demonstration:

The following query finds the product code, MSRP, and the deviation of product MSRP from the average of 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)

Here is the output:

Letâ€™s break down the calculation of the deviation column.

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, the following formula returns the difference between MSRP and average product line MSRP:

`msrp - AVG(msrp) OVER (PARTITION BY productLine) `

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

Third, the `ROUND()` function rounds the deviation to zero decimal.

`ROUND( msrp - AVG(msrp) OVER ( PARTITION BY productLine ) ) `

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

Fourth, because some products have MSRP lower than the average MSRP, their deviations from the average value are negative. The `ABS()` function applied to all deviations to return the absolute values.

In this tutorial, you have learned how to use the MySQL `ABS()` function to get the absolute value of a number.