ABS()

The absolute function, abs(), returns the absolute value result of an arithmetic expression in the query. For example, Open and Close stock price differences can be both negative and positive depending on the trading day. However, if we are interested in understanding the variance of the difference between the two prices, use their absolute difference is a much better data point. Let's implement this in a query.

For this query, we simply look at the difference between the cost and price , and apply the abs() to demonstrate it's use.

select product_name, 
    cost - price AS neg_loss, 
    ABS(cost - price) AS negative_cost 
FROM products LIMIT 10;

    product_name     | neg_loss | negative_cost
---------------------+----------+---------------
Samsung Galaxy S23   |  -279.99 |        279.99
MacBook Pro 16"      |  -699.99 |        699.99
Dell XPS 13          |  -399.99 |        399.99
iPad Air             |  -199.99 |        199.99
AirPods Pro          |   -99.99 |         99.99
Sony WH-1000XM4      |  -149.99 |        149.99
Nintendo Switch      |  -119.99 |        119.99
PlayStation 5        |  -149.99 |        149.99
Xbox Series X        |  -149.99 |        149.99
Apple Watch Series 8 |  -149.99 |        149.99
(10 rows)

In the above query results, we can compare both the output of the arithmetic difference and absolute difference. The arithmetic difference contains positive and negative values which indicate the direction of the cost and price difference while the absolute only shows the magnitude. Both can be useful in their own context.