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.