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;Output:
productName productLine msrp deviation
------------------------------------------- ---------------- ------ ---------
18th century schooner Ships 122.89 36
18th Century Vintage Horse Carriage Vintage Cars 104.72 18
1900s Vintage Bi-Plane Planes 68.51 21
1900s Vintage Tri-Plane Planes 72.45 17
1903 Ford Model A Vintage Cars 136.59 49
1904 Buick Runabout Vintage Cars 87.77 1
1911 Ford Town Car Vintage Cars 60.54 27
1912 Ford Model T Delivery Wagon Vintage Cars 88.51 1
1913 Ford Model T Speedster Vintage Cars 101.31 14
1917 Grand Touring Sedan Vintage Cars 170.00 83
1917 Maxwell Touring Car Vintage Cars 99.21 12
1926 Ford Fire Engine Trucks and Buses 60.77 42
1928 British Royal Navy Airplane Planes 109.42 20
1928 Ford Phaeton Deluxe Vintage Cars 68.79 18
1928 Mercedes-Benz SSK Vintage Cars 168.75 82
1930 Buick Marquette Phaeton Vintage Cars 43.64 43
1932 Alfa Romeo 8C2300 Spider Sport Vintage Cars 92.03 5
1932 Model A Ford J-Coupe Vintage Cars 127.13 40
1934 Ford V8 Coupe Vintage Cars 62.46 25
1936 Chrysler Airflow Vintage Cars 97.39 10
1936 Harley Davidson El Knucklehead Motorcycles 60.57 37
1936 Mercedes Benz 500k Roadster Vintage Cars 41.03 46
1936 Mercedes-Benz 500K Special Roadster Vintage Cars 53.91 33
1937 Horch 930V Limousine Vintage Cars 65.75 21
1937 Lincoln Berline Vintage Cars 102.74 16
1938 Cadillac V-16 Presidential Limousine Vintage Cars 44.80 42
1939 Cadillac Limousine Vintage Cars 50.31 37
1939 Chevrolet Deluxe Coupe Vintage Cars 33.19 54
1940 Ford Delivery Sedan Vintage Cars 83.86 3
1940 Ford Pickup Truck Trucks and Buses 116.67 13
1940s Ford truck Trucks and Buses 121.08 18
1941 Chevrolet Special Deluxe Cabriolet Vintage Cars 105.87 19
1948 Porsche 356-A Roadster Classic Cars 77.00 41
1948 Porsche Type 356 Roadster Classic Cars 141.28 23
1949 Jaguar XK 120 Classic Cars 90.87 27
1950's Chicago Surface Lines Streetcar Trains 62.14 12
1952 Alpine Renault 1300 Classic Cars 214.30 96
1952 Citroen-15CV Classic Cars 117.44 1
1954 Greyhound Scenicruiser Trucks and Buses 54.11 49
1956 Porsche 356A Coupe Classic Cars 140.43 22
1957 Chevy Pickup Trucks and Buses 118.50 15
1957 Corvette Convertible Classic Cars 148.80 31
1957 Ford Thunderbird Classic Cars 71.27 47
1957 Vespa GS150 Motorcycles 62.17 35
1958 Chevy Corvette Limited Edition Classic Cars 35.36 83
1958 Setra Bus Trucks and Buses 136.67 33
1960 BSA Gold Star DBD34 Motorcycles 76.17 21
1961 Chevrolet Impala Classic Cars 80.84 37
1962 City of Detroit Streetcar Trains 58.58 15
1962 LanciaA Delta 16V Classic Cars 147.74 30
1962 Volkswagen Microbus Trucks and Buses 127.79 25
1964 Mercedes Tour Bus Trucks and Buses 122.73 20
1965 Aston Martin DB5 Classic Cars 124.44 6
1966 Shelby Cobra 427 S/C Classic Cars 50.31 68
1968 Dodge Charger Classic Cars 117.44 1
1968 Ford Mustang Classic Cars 194.57 77
1969 Chevrolet Camaro Z28 Classic Cars 85.61 32
1969 Corvair Monza Classic Cars 151.08 33
1969 Dodge Charger Classic Cars 115.16 3
1969 Dodge Super Bee Classic Cars 80.41 38
1969 Ford Falcon Classic Cars 173.02 55
1969 Harley Davidson Ultimate Chopper Motorcycles 95.70 1
1970 Chevy Chevelle SS 454 Classic Cars 73.49 45
1970 Dodge Coronet Classic Cars 57.80 60
1970 Plymouth Hemi Cuda Classic Cars 79.80 38
1970 Triumph Spitfire Classic Cars 143.62 26
1971 Alpine Renault 1600s Classic Cars 61.23 57
1972 Alfa Romeo GTA Classic Cars 136.00 18
1974 Ducati 350 Mk3 Desmo Motorcycles 102.05 5
1976 Ford Gran Torino Classic Cars 146.99 29
1980’s GM Manhattan Express Trucks and Buses 96.31 7
1980s Black Hawk Helicopter Planes 157.69 68
1982 Camaro Z28 Classic Cars 101.15 17
1982 Ducati 900 Monster Motorcycles 69.26 28
1982 Ducati 996 R Motorcycles 40.23 57
1982 Lamborghini Diablo Classic Cars 37.76 80
1985 Toyota Supra Classic Cars 107.57 10
1992 Ferrari 360 Spider red Classic Cars 169.34 51
1992 Porsche Cayenne Turbo Silver Classic Cars 118.28 0
1993 Mazda RX-7 Classic Cars 141.54 24
1995 Honda Civic Classic Cars 142.25 24
1996 Moto Guzzi 1100i Motorcycles 118.94 22
1996 Peterbilt 379 Stake Bed with Outrigger Trucks and Buses 64.64 39
1997 BMW F650 ST Motorcycles 99.89 3
1997 BMW R 1100 S Motorcycles 112.70 16
1998 Chrysler Plymouth Prowler Classic Cars 163.73 46
1999 Indy 500 Monte Carlo SS Classic Cars 132.00 14
1999 Yamaha Speed Boat Ships 86.02 1
2001 Ferrari Enzo Classic Cars 207.80 90
2002 Chevy Corvette Classic Cars 107.08 11
2002 Suzuki XREO Motorcycles 150.62 53
2002 Yamaha YZR M1 Motorcycles 81.36 16
2003 Harley-Davidson Eagle Drag Bike Motorcycles 193.66 96
America West Airlines B757-200 Planes 99.72 10
American Airlines: B767-300 Planes 91.34 2
American Airlines: MD-11S Planes 74.03 15
ATA: B757-300 Planes 118.65 29
Boeing X-32A JSF Planes 49.66 40
Collectable Wooden Train Trains 100.84 27
Corsair F4U ( Bird Cage) Planes 68.24 21
Diamond T620 Semi-Skirted Tanker Trucks and Buses 115.75 13
F/A 18 Hornet 1/72 Planes 80.00 10
HMS Bounty Ships 90.52 4
P-51-D Mustang Planes 84.48 5
Pont Yacht Ships 54.60 32
The Mayflower Ships 86.61 0
The Queen Mary Ships 99.31 13
The Schooner Bluenose Ships 66.67 20
The Titanic Ships 100.17 14
The USS Constitution Ship Ships 72.28 14 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.