Numeric Functions

SQL numeric functions are operations used to perform mathematical computations on numeric data types in a database. They include functions like ABS (returns the absolute value), ROUND (rounds a number to a specified number of decimal places), CEILING (returns the smallest integer greater than or equal to a given number), FLOOR (returns the largest integer less than or equal to a given number), and SUM (calculates the sum of values). These functions help manipulate numeric data within SQL queries for various analytical and reporting purposes.

ABS: The SQL ABS function returns the absolute value of a numeric expression. This means it returns the positive value of the given number, regardless of its sign. Here's an example to illustrate its usage.

SELECT Number, ABS(Number) AS AbsoluteValue FROM Numbers;

In this example, the ABS function is applied to each value in the Number column of the Numbers table, returning its absolute value. So, negative numbers become positive, and positive numbers remain unchanged.

 

ROUND: The SQL ROUND function is used to round a numeric value to a specified number of decimal places.

Consider a table named ProductSales with the following columns: ProductID and UnitPrice. We want to retrieve the rounded unit prices of products to two decimal places.

SELECT ProductID, ROUND(UnitPrice, 2) AS RoundedPrice FROM ProductSales;

In this example, the ROUND function is applied to the UnitPrice column, rounding each value to two decimal places. The result set will contain the ProductID along with the rounded prices in the RoundedPrice column.

 

CEILING: The SQL CEILING function returns the smallest integer greater than or equal to a specified numeric expression.

Consider a table named Orders with the following columns: OrderID and TotalAmount. We want to retrieve the ceiling value of the total amount for each order.

SELECT OrderID, CEILING(TotalAmount) AS RoundedTotalAmount FROM Orders;

In this example, the CEILING function is applied to the TotalAmount column, returning the smallest integer greater than or equal to each total amount. The result set will include the OrderID along with the rounded total amount in the RoundedTotalAmount column.

 

FLOOR: The SQL FLOOR function returns the largest integer less than or equal to a specified numeric expression.

Consider a table named Products with the following columns: ProductID and Price. We want to retrieve the floor value of the price for each product.

SELECT ProductID, FLOOR(Price) AS FlooredPrice FROM Products;

In this example, the FLOOR function is applied to the Price column, returning the largest integer less than or equal to each price. The result set will include the ProductID along with the floored price in the FlooredPrice column.

 

SQRT: The SQL SQRT function is used to calculate the square root of a numeric expression.

Consider a table named Circle with the following columns: CircleID and Radius. We want to calculate the square root of the radius for each circle.

SELECT CircleID, SQRT(Radius) AS SquareRootRadius FROM Circle;

In this example, the SQRT function is applied to the Radius column, computing the square root of each radius. The result set will include the CircleID along with the calculated square root of the radius in the SquareRootRadius column.