SQL ABS() function

Absolute value function

SQL ABS() function is used to get the absolute value of a number passed as an argument.

Syntax:

ABS(expression)

Parameters:

MySQL, PostgreSQL, Oracle and SQL Server

All of above platforms support the SQL syntax of ABS().

SQL ABS() function: Visual presentation

Example :

To get the absolute value of the number -17.36 from the DUAL table, the following SQL statement can be used :

SELECT ABS(-17.36) -- Selecting the absolute value of -17.36
FROM dual; -- Selecting from the 'dual' pseudo-table

Explanation:

• The query starts with the SELECT statement, indicating that it will retrieve data from the database.
• It uses the ABS() function to calculate the absolute value of the number provided, which in this case is -17.36.
• The result of the ABS() function will be the positive value of the number provided. In this case, the absolute value of -17.36 is 17.36.
• The query selects this calculated absolute value.
• The FROM dual statement is used to ensure that the SELECT statement has something to select from. The dual pseudo-table is a special table in Oracle database that always contains one row. It is often used for calculations or for selecting literal values.

Output:

ABS(-17.36)
-----------
      17.36

SQL ABS() function with distinct clause

 
+------------+----------------------+--------------------+------------+-----------------+---------+
| AGENT_CODE | AGENT_NAME           | WORKING_AREA       | COMMISSION | PHONE_NO        | COUNTRY |
+------------+----------------------+--------------------+------------+-----------------+---------+
| A007       | Ramasundar           | Bangalore          |       0.15 | 077-25814763    |         |
| A003       | Alex                 | London             |       0.13 | 075-12458969    |         |
| A008       | Alford               | New York           |       0.12 | 044-25874365    |         |
| A011       | Ravi Kumar           | Bangalore          |       0.15 | 077-45625874    |         |
| A010       | Santakumar           | Chennai            |       0.14 | 007-22388644    |         |
| A012       | Lucida               | San Jose           |       0.12 | 044-52981425    |         |
| A005       | Anderson             | Brisban            |       0.13 | 045-21447739    |         |
| A001       | Subbarao             | Bangalore          |       0.14 | 077-12346674    |         |
| A002       | Mukesh               | Mumbai             |       0.11 | 029-12358964    |         |
| A006       | McDen                | London             |       0.15 | 078-22255588    |         |
| A004       | Ivan                 | Torento            |       0.15 | 008-22544166    |         |
| A009       | Benjamin             | Hampshair          |       0.11 | 008-22536178    |         |
+------------+----------------------+--------------------+------------+-----------------+---------+

To get unique absolute value of the column 'commission' after multiplying by (-1) with a column alias "DISTINCT(ABS())" from the 'agents' table, the following SQL statement can be used :

-- This SQL statement selects distinct absolute values of negative commission amounts from the 'agents' table.

SELECT DISTINCT(ABS(commission*(-1))) "DISTINCT(ABS())"
-- SELECT statement retrieves data from the database
-- DISTINCT keyword ensures that only unique values are returned
-- ABS() function returns the absolute value of a number
-- commission*(-1) negates the commission values to ensure they're negative

FROM agents;
-- Specifies the table 'agents' from which the data is being retrieved

Explanation:

• This SQL query is designed to retrieve distinct absolute values of negative commission amounts from the 'agents' table.
• The ABS() function is used to ensure that any negative commission values are converted into positive values.
• The DISTINCT keyword ensures that only unique absolute values are returned, preventing duplicates.
• The commission*(-1) operation is used to ensure that the commission values are negated, converting positive commissions into negative ones for the ABS() function to correctly calculate the absolute values.
• The query is executed against the 'agents' table to retrieve the required data.

Output:

DISTINCT(ABS())
---------------
            .15
            .11
            .14
            .13
            .12

Note: Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Here is a slide presentation which covers the SQL arithmetic functions.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL Arithmetic function
Next:CEIL