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.