ABS() Function in SQL

The SQL ABS() function is commonly used in financial calculations, distance calculations, scientific applications and data analysis where only the magnitude of a number is required regardless of whether it is positive or negative.

What is the ABS() Function in SQL?

This function in SQL is a mathematical function that returns the absolute (positive) value of a number. If the given number is negative then the function removes the negative sign and returns the positive value. If the number is already positive or zero then it returns the same value without making any changes.

For example:

• ABS(-250) returns 250
• ABS(150) returns 150
• ABS(0) returns 0

This function works with integers, decimal values, floating-point numbers and values returned by mathematical expressions.

Syntax:

Parameters:

number: It is the numeric value whose absolute value needs to be returned.

Return Value:

The ABS() function returns the absolute value of the given numeric expression.

• If the value is negative then the negative sign is removed.
• If the value is positive then the same value is returned.
• If the value is zero then zero is returned.
• If the input is NULL then the function returns NULL.

Creating a table

Let us create a table named EmployeeScores that stores the information about the employees and contains the following fields:

ID: It stores the unique identification number of each employee.

Name: It stores the employee name.

Department: It stores the department in which the employee works.

ScoreDifference: It stores the difference in performance score. Positive values indicate improvement, while negative values indicate a decrease.

SalaryChange: It stores the amount by which the employee's salary changed. Positive values represent salary increments, whereas negative values represent salary deductions.

SQL Query:

Inserting Records

Now we will add some values to the table.

SQL Query:

Initial table:

Output:

Examples of ABS() Function in SQL

Here we will learn how to use the ABS() function in SQL to convert negative score

Example 1: Finding the Absolute Value of Score Difference

Here we will learn how to use the ABS() function in SQL to convert negative score differences into positive values. This example helps us understand how the SQL ABS() function simplifies mathematical calculations and makes performance comparisons easier during SQL data analysis.

SQL Query:

Output:

Explanation:

The output shows that every negative score difference has been converted into a positive value while positive values and zero remain unchanged. It makes it easier to compare the size of score changes without considering their direction.

Example 2: Finding the Absolute Value of Salary Changes

Here we will learn how to utilize the ABS() function in SQL to display the absolute value of salary changes.

SQL Query:

Output:

Explanation:

The output removes the negative sign from salary deductions and displays every salary change as a positive amount. It assists users easily compare the magnitude of salary changes regardless of whether they were increases or decreases.

Example 3: Using ABS() with a Mathematical Expression

Here we will learn how to use the ABS() function in SQL with a mathematical expression instead of a single column value.

SQL Query:

Output:

Explanation:

The output calculates the difference between each score and 20 before applying the ABS() function. The final result always shows a positive distance from 20 which makes comparisons much easier.

Example 4: Sorting Employees Using Absolute Score Difference

Here we will learn how to use the ABS() function in SQL together with the ORDER BY clause.

SQL Query:

Output:

Explanation:

The output sorts the employees according to the largest absolute score difference instead of the actual positive or negative value. It makes it easy to identify employees with the biggest overall changes irrespective of the direction of those changes.

Example 5: Finding the Absolute Value of a Constant Number

In this example, we will use the ABS() function with a constant negative value. It helps us understand how the function works without using any table data.

SQL Query:

Output:

Explanation:

The ABS() function removes the negative sign from -500 and returns 500. Since the purpose of the function is to return the absolute value so the result is always non-negative.

Example 6: Finding Employees with Salary Change Greater Than $1000

In this example, we will calculate the employees whose Salary Change is greater than $1000 using the WHERE clause.

SQL Query:

Output:

Explanation:

The ABS() function converts all salary changes into positive values before the comparison is made. Only employees whose salary change exceeds 1000 in magnitude are displayed.

Example 7: Calculating the Total Absolute Score Difference

In this example, we will combine the ABS() function with the SUM() aggregate function to calculate the total magnitude of score changes across all employees.

SQL Query:

Output:

Explanation:

The ABS() function first converts all score differences into positive values. After that, the SUM() function adds them together. It provides the total magnitude of score changes without considering whether the scores increased or decreased.

Example 8: Displaying Absolute Salary Changes Rounded Up Using ABS() and CEIL()

We will find the absolute value of salary changes and round them upward to the nearest integer using the CEIL() function.

SQL Query:

Output:

Explanation:

The ABS() function converts negative salary changes into positive values and the CEIL() function rounds each value up to the next whole number.

Example 9: Using ABS() with HAVING to Find Departments with High Score Variations

Here we will display only those departments where the average score difference exceeds 20 when the sign of the values is ignored.

SQL Query:

Output:

Explanation:

The ABS() function converts all score differences into positive values before calculating the average. The HAVING clause then filters departments whose average score difference is greater than 20.

Behavior of the ABS() Function in SQL

• The ABS() function always returns the absolute (non-negative) value of a numeric expression.
• If the input value is negative then the function removes the negative sign and returns the positive value.
• If the input value is already positive then the same value is returned without any change.
• If the input value is 0 then the function returns 0.
• The function can be used with integers, decimal numbers, floating-point values and mathematical expressions.
• ABS() can be combined with other SQL functions such as ROUND(), CEIL(), FLOOR() and arithmetic operators.
• It can be used in SQL clauses such as SELECT, WHERE, ORDER BY, GROUP BY and HAVING.
• If the input value is NULL then the function returns NULL.
• The return value always has the same numeric data type as the input expression (subject to database-specific rules).