{"id":7225,"date":"2018-09-01T02:56:15","date_gmt":"2018-09-01T09:56:15","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=7225"},"modified":"2023-10-07T01:29:40","modified_gmt":"2023-10-07T08:29:40","slug":"mysql-abs-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-math-functions\/mysql-abs-function\/","title":{"rendered":"MySQL ABS Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL ABS()<\/code> function to return the absolute value of a number.<\/p>\n\n\n\n

MySQL ABS() function overview<\/h2>\n\n\n\n

The ABS()<\/code> function is a mathematical function that returns the absolute (no-negative) value of a numeric expression or a numeric column. <\/p>\n\n\n\n

The ABS()<\/code> function removes the sign of a number. So if the input is positive or negative, it always returns a positive number or zero.<\/p>\n\n\n\n

Here’s the syntax of the ABS()<\/code> function:<\/p>\n\n\n

ABS(numeric_expression)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, numeric_expression<\/code> is a literal number or an expression that evaluates to a number. <\/p>\n\n\n\n
If numeric_expression<\/code> is a negative number, the ABS()<\/code> function returns the positive value of the negative value. If numeric_expression<\/code> is zero or positive, ABS()<\/code> function has no effect.<\/p>\n\n\n\n
The data type of the return value is the same as the data type of the input argument.<\/p>\n\n\n\n
MySQL ABS() function examples<\/h2>\n\n\n\n
Let’s take some examples of using the ABS()<\/code> function.<\/p>\n\n\n\n
1) Simple ABS() function example<\/h3>\n\n\n\n
The following example uses the ABS function to return the absolute values of three different numbers:<\/p>\n\n\n
SELECT<\/span> \n    ABS<\/span>(-10<\/span>), \n    ABS<\/span>(0<\/span>), \n    ABS<\/span>(10<\/span>);  <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
In this example, the ABS()<\/code> function returns the absolute value of -10, which is 10. It doesn’t have any effect on the 0 and 10.<\/p>\n\n\n\n
2) Using ABS() function on table columns<\/h3>\n\n\n\n
We will use the products<\/code> table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"products<\/figure>\n\n\n\n
The following query uses the ABS()<\/code> function to find the product code, MSRP, and the deviation of product MSRP from the average MSRP of the product within its product line.<\/p>\n\n\n
SELECT<\/span> \n    productName, \n    productLine,\n    msrp,\n    ABS<\/span>(\n        ROUND<\/span>(\n            msrp - AVG<\/span>(msrp) OVER<\/span> (\n                PARTITION<\/span> BY<\/span> productLine\n            )\n        )\n    ) deviation\nFROM<\/span>\n    products\nORDER<\/span> BY<\/span> \n    productName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
How it works.<\/p>\n\n\n\n
First, the AVG()<\/code> window function<\/a> returns the average MSRP of each product within its product line:<\/p>\n\n\n
AVG(msrp) OVER ( PARTITION BY productLine )<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, use the following formula to return the difference between MSRP and average product line MSRP:<\/p>\n\n\n
msrp - AVG(msrp) OVER (PARTITION BY productLine)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
ROUND(\n     msrp - AVG(msrp) OVER (\n        PARTITION BY productLine\n       )\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, use the ABS()<\/code> 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. <\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the ABS()<\/code> function to get the absolute value of a number.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Third, use the ROUND()<\/a><\/code> function to round the deviation to zero decimals.<\/p>\n\n\n