SQL Math Functions: ABS, CEIL, FLOOR, ROUND Examples

In the previous tutorials, we covered arithmetic calculations in SQL. Building upon that knowledge, we're now introducing 6 more mathematical functions that enable us to do even more complex calculations!

Let's work through a Bloomberg SQL question that illustrates how these functions are applied in real-world scenarios.

ABS(): Calculating Absolute Differences

Imagine you're tasked with preparing a dashboard showcasing the historical performance of Google's stock (ticker: 'GOOG') throughout 2023. The finance team specifically requests the absolute difference between the opening and closing prices for each trading day.

We can utilize the ABS() function which calculates the absolute value of a number, irrespective of its sign.

SELECT 
  date, 
  ticker,
  (close-open) AS difference,
  ABS(close-open) AS abs_difference
FROM stock_prices
WHERE EXTRACT(YEAR FROM date) = 2023
  AND ticker = 'GOOG';

In this example, we compute the difference between closing and opening prices for each day using the ABS() function and returns its absolute value.

See how in the (close-open) calculation, you end up with a difference of -9.44, including the negative sign? But with the helpful ABS(close-open) function, you get a positive difference of 9.44 without the negative sign.

ROUND(): Rounding Numbers

Consider a scenario where you're compiling a report displaying the average closing prices of Apple's stock (ticker: 'AAPL') throughout 2022. To ensure the report's readability, you decide to round the average prices to 2 decimal places.

The ROUND() function is handy here; it rounds a number to a specified number of decimal places.

SELECT 
  ticker,
  AVG(close) AS avg_close,
  ROUND(AVG(close), 2) AS rounded_avg_close
FROM stock_prices
WHERE EXTRACT(YEAR FROM date) = 2022
GROUP BY ticker;

In the initial AVG(close) calculation, the output displays with 10 decimal places which honestly just looks unpleasant. However, by using ROUND(AVG(close), 2), you specify the number of decimals you prefer, resulting in a neatly rounded up price.

We'll cover more about rounding, and the annoyances of integers vs. floating-point decimal numbers in the SQL division tutorial later.

CEIL() and FLOOR(): Rounding Up and Down

Now, imagine you're analyzing Facebook's stock performance (ticker: 'META') to identify potential trading ranges.

We can utilize the CEIL() function to round up and the FLOOR() function to round down to the lowest and highest prices.

SELECT 
  date,
  ticker,
  high,
  CEIL(high) AS resistance_level,
  low,
  FLOOR(low) AS support_level
FROM stock_prices
WHERE ticker = 'META'
ORDER BY date;

By employing FLOOR() on the low column, we highlight potential support levels by rounding down the lowest prices. Conversely, with CEIL() on the high column, we identify potential resistance levels by rounding up the highest prices.

POWER(): Calculating Squared Values

In this scenario, we'll explore the concept of squared values by focusing on the 3 popular tech stocks: Apple ('AAPL'), Google ('GOOG'), and Microsoft ('MSFT'). Although this scenario is simplified for illustrative purposes, it offers a straightforward way to introduce the usage of the POWER() function.

The POWER() function raises a number to a specified power, allowing us to perform exponential calculations.

SELECT 
  date,
  ticker,
  close,
  ROUND(POWER(close, 2),2) AS squared_close
FROM stock_prices
WHERE ticker IN ('AAPL', 'GOOG', 'MSFT')
ORDER BY date;

In this query, we're leveraging the POWER() function to calculate the squared values of the closing prices for the selected tech stocks.

For shorthand, in PostgreSQL, we can also just use the ^ operator too!

MOD() or %

Imagine you're intrigued by stocks that exhibit a consistent price behavior divisible by 5.

The MOD() function or % modulo operator calculates the remainder of division between two numbers.

SELECT 
  ticker,
  close,
  MOD(close, 5) AS price_remainder_mod,
  close%5 AS price_remainder_modulo
FROM stock_prices
WHERE ticker = 'GOOG';

In this example, MOD() calculates the remainder when the closing prices are divided by 5. The % operator achieves the same result. Both approaches help in identifying stocks with price behaviors divisible by 5.

SQL CEIL Practice Exercise

In the following problem, we'll analyze the CVS pharmacy sales dataset, and try to find the per unit cost of medicines made by Merck. We'll round our unit cost up to the nearest dollar, courtesy of CEIL().

The output should look like this:

Practice Problem
Use CEIL to find the per unit cost for each Merck drug sold at CVS Pharmacy, rounded up to the nearest dollar.
Try it out

What's Next: SQL Division

In the next tutorial, we'll laser focus on SQL division, talk more about tricks to round numbers, and how to CAST between different data-types.

Next Tutorial: SQL Division