In SQL, we have access to the abs() function that allows us to convert a negative number into positive values and while leaving the positive numbers unchanged.
Absolute values are a common occurrence especially when dealing with differences between two values or calculating the coordinate-based results.
In this tutorial, we will explore the various methods that we can use to calculate the absolute value in SQL. We will use MySQL as our database engine of choice and the Sakila sample database. However, feel free to use any dataset that you wish.
Method 1: MySQL ABS() Function
The most common and straightforward method of calculating the absolute value in MySQL is using the ABS() function. It is good to keep in mind that this function is part of ANSI SQL. Hence, you will find it in other databases such as PostgreSQL and SQL Server.
The function takes a numeric expression as an argument and returns its absolute value. The syntax is as follows:
Take for example the basic usage as follows:
As you can guess, the previous query converts the provided value into its positive representation as shown in the following output:
--------------+
15|
Method 2: Using the Abs() Function in a Table
We can also use the abs() function in a table to calculate the absolute values of all the provided sets.
Consider an example table as follows:
value DECIMAL(10, 2)
);
INSERT INTO numerical_data (value) VALUES
(10.5),
(-3.75),
(25.0),
(-7.2),
(12.8),
(-15.3);
In this case, we have a basic table that contains the numeric data, both positive and negative decimal values.
To calculate the absolute values for all the rows that are stored in the table, we can run the query as follows:
ABS(value) AS absolute_values
FROM numerical_data;
This should convert the resulting values into their positive representation.
An example output is as follows:
---------------+
10.50|
3.75|
25.00|
7.20|
12.80|
15.30|
We can also convert the values from their positive representation into negative by adding the minus sign to the abs() function.
-ABS(value) AS absolute_values
FROM numerical_data;
The resulting output is as follows:
---------------+
-10.50|
-3.75|
-25.00|
-7.20|
-12.80|
-15.30|
By changing the sign on the function, you can convert the absolute values from positive to negative representation and vice versa.
Conclusion
In this tutorial, we learned how to calculate the absolute value of numerical values in SQL using the abs() function. We also use the -abs() function which can convert the value into negative absolute values.
