Mathematical Functions - ABS, ROUND, CEIL, FLOOR in SQL

SQL provides several mathematical functions that allow you to perform operations on numeric data types. These functions help in rounding numbers, computing absolute values, and adjusting decimal points. In this article, we will explore some of the most commonly used mathematical functions in SQL: ABS, ROUND, CEIL, and FLOOR.

1. ABS - Get the Absolute Value of a Number

The ABS function is used to return the absolute value of a number. This means it will remove any negative sign, returning only the magnitude of the number as a positive value.

Syntax:

      SELECT ABS(number);
    

ABS takes a numeric argument and returns the absolute value of that number.

Example: Using ABS

      -- Get the absolute value of a negative number
      SELECT ABS(-45) AS AbsoluteValue;
    

This query returns the absolute value of -45, which is 45.

Example: Using ABS with Table Data

      -- Get the absolute difference in sales for each product
      SELECT ProductName, ABS(Sales - TargetSales) AS SalesDifference
      FROM Products;
    

This query calculates the absolute difference between actual Sales and TargetSales for each product in the Products table.

2. ROUND - Round a Number to a Specified Decimal Place

The ROUND function is used to round a numeric value to a specified number of decimal places. You can round to the nearest whole number, or you can specify the number of decimal places to keep.

Syntax:

      SELECT ROUND(number, decimal_places);
    

ROUND takes two arguments: the numeric value to round and the number of decimal places to keep.

Example: Using ROUND

      -- Round a number to 2 decimal places
      SELECT ROUND(45.6789, 2) AS RoundedValue;
    

This query rounds the number 45.6789 to two decimal places, resulting in 45.68.

Example: Using ROUND with Table Data

      -- Round the price of each product to the nearest whole number
      SELECT ProductName, ROUND(Price, 0) AS RoundedPrice
      FROM Products;
    

This query rounds the Price of each product to the nearest whole number in the Products table.

3. CEIL - Round a Number Up to the Nearest Integer

The CEIL (Ceiling) function is used to round a number up to the nearest integer. This means that even if the decimal value is small, it will be rounded up to the next whole number.

Syntax:

      SELECT CEIL(number);
    

CEIL takes a numeric value and returns the smallest integer greater than or equal to the number.

Example: Using CEIL

      -- Round a number up to the next integer
      SELECT CEIL(45.2) AS RoundedUp;
    

This query rounds the number 45.2 up to the nearest integer, resulting in 46.

Example: Using CEIL with Table Data

      -- Round up the price of each product to the next whole number
      SELECT ProductName, CEIL(Price) AS RoundedPrice
      FROM Products;
    

This query rounds up the Price of each product to the next whole number in the Products table.

4. FLOOR - Round a Number Down to the Nearest Integer

The FLOOR function is used to round a number down to the nearest integer. This means that even if the decimal value is small, it will be rounded down to the next whole number.

Syntax:

      SELECT FLOOR(number);
    

FLOOR takes a numeric value and returns the largest integer less than or equal to the number.

Example: Using FLOOR

      -- Round a number down to the previous integer
      SELECT FLOOR(45.8) AS RoundedDown;
    

This query rounds the number 45.8 down to the nearest integer, resulting in 45.

Example: Using FLOOR with Table Data

      -- Round down the price of each product to the previous whole number
      SELECT ProductName, FLOOR(Price) AS RoundedPrice
      FROM Products;
    

This query rounds down the Price of each product to the previous whole number in the Products table.

5. Conclusion

SQL mathematical functions such as ABS, ROUND, CEIL, and FLOOR are useful for performing various calculations and rounding operations on numeric data. These functions can be applied to clean up or manipulate data in your database, especially when working with financial, statistical, or other numerical information. By using these functions, you can refine your SQL queries to perform accurate and meaningful calculations.