What is Mathematical Functions in SQL SERVER PART 14 With Example Code

These functions can be used in SELECT queries, WHERE clauses, or in conjunction with other SQL statements to perform mathematical operations and calculations on numeric data stored in the database.If you have any specific questions about these functions or how to use them, feel free to ask!

 

 -- Drop the Sales table if it exists
IF OBJECT_ID('Sales', 'U') IS NOT NULL
    DROP TABLE Sales;
 
-- Creating a table to store sales data
CREATE TABLE Sales (
    SalesID INT PRIMARY KEY,
    Amount DECIMAL(10, 2),
    Quantity INT
);
 
-- Inserting sample data into the Sales table
INSERT INTO Sales (SalesID, Amount, Quantity)
VALUES
    (1, 100.50, 3),
    (2, 75.25, 2),
    (3, 50.00, 5),
    (4, 120.75, 1),
    (5, 90.30, 4);
 
-- Calculate total sales amount, average sales amount, square of quantity, power of quantity, and count of sales records
SELECT
    SUM(Amount) AS TotalSalesAmount,
    AVG(Amount) AS AverageSalesAmount,
    SQUARE(SUM(Quantity)) AS QuantitySquare,
    POWER(SUM(Quantity), 2) AS QuantityPower,
    COUNT(*) AS NumberOfSales
FROM Sales;
 
-- Sample data
DECLARE @number FLOAT = 7.89;
 
-- Performing mathematical operations and functions on scalar variables
SELECT
    @number AS OriginalNumber,
    @number + 3 AS AdditionResult,
    @number * 2 AS MultiplicationResult,
    ROUND(@number / 2, 2) AS DivisionResult,
    POWER(@number, 2) AS Square,
    SQRT(@number) AS SquareRoot,
    EXP(@number) AS Exponential,
    LOG(@number) AS NaturalLogarithm;
 
-- Applying mathematical functions to scalar values
SELECT
    ABS(-7) AS AbsoluteValue,
    ROUND(3.14159, 2) AS RoundedValue,
    CEILING(4.25) AS CeilingValue,
    FLOOR(4.75) AS FloorValue,
    POWER(2, 3) AS PowerValue,
    SQRT(25) AS SquareRoot,
    EXP(1) AS ExponentialValue,
    LOG(10) AS NaturalLogarithm;
 
-- Sample data for arithmetic operations
DECLARE @value INT = 9;
DECLARE @decimalValue DECIMAL(10, 2) = 8.75;
 
-- Performing arithmetic operations and mathematical functions on scalar variables
SELECT
    @value + 3 AS Sum,
    15 - @value AS Difference,
    @value * 2 AS Product,
    20 / @value AS Quotient;
 
-- Applying mathematical functions to scalar values (continued)
SELECT
    ABS(-7) AS AbsoluteValue,
    ROUND(@decimalValue, 1) AS RoundedValue,
    CEILING(@decimalValue) AS CeilingValue,
    FLOOR(@decimalValue) AS FloorValue,
    POWER(2, 3) AS PowerValue,
    SQRT(25) AS SquareRoot,
    EXP(2) AS ExponentialValue,
    LOG(10) AS NaturalLogarithm; 

       -- Addition
SELECT 5 + 3 AS Addition; -- Output: 8
 
-- Subtraction
SELECT 10 - 5 AS Subtraction; -- Output: 5
 
-- Multiplication
SELECT 4 * 3 AS Multiplication; -- Output: 12
 
-- Division
SELECT 20 / 5 AS Division; -- Output: 4
 
-- Modulus
SELECT 10 % 3 AS Modulus; -- Output: 1
 
-- Negation
SELECT -5 AS Negation; -- Output: -5
 
-- Absolute Value
SELECT ABS(-10) AS AbsoluteValue; -- Output: 10