SQL Standard

SQL Absolute Value

In mathematics, an absolute value represents the magnitude or distance of a numeric value from zero without considering the sign of the value.

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:

SELECT ABS(numeric_expression) AS absolute_value;

Take for example the basic usage as follows:

SELECT ABS(-15) AS absolute_value;

As you can guess, the previous query converts the provided value into its positive representation as shown in the following output:

absolute_value|
--------------+
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:

CREATE TABLE numerical_data (

  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:

SELECT

ABS(value) AS absolute_values

FROM numerical_data;

This should convert the resulting values into their positive representation.

An example output is as follows:

absolute_values|
---------------+
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.

SELECT

-ABS(value) AS absolute_values

FROM numerical_data;

The resulting output is as follows:

absolute_values|
---------------+
-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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list