Mathematical and Conversion Functions

Mathematical functions perform calculations on numeric data—rounding values, computing averages, finding minimums and maximums, calculating percentages, and more. Conversion functions transform data between types—converting strings to numbers, numbers to strings, handling NULLs, and casting data types. Whether you're calculating tax amounts, rounding prices to two decimals, converting string inputs to integers, computing statistical measures, or generating random values for testing, these functions are essential tools. This lesson covers the most commonly-used mathematical and conversion functions with practical examples showing how to perform calculations, format numbers, and handle type conversions in your queries.

📚 What You'll Learn

• How to round numbers with ROUND, CEIL, FLOOR, TRUNCATE
• How to use absolute value and sign with ABS and SIGN
• How to calculate powers and roots with POW, SQRT, EXP, LOG
• How to generate random numbers with RAND
• How to use trigonometric functions (SIN, COS, TAN)
• How to format numbers with FORMAT
• How to convert types with CAST and CONVERT
• How to handle NULLs with COALESCE, IFNULL, NULLIF
• How to use conditional logic with IF and CASE
• Practical patterns for calculations and conversions

1. Rounding Numbers

ROUND - Standard Rounding

-- ROUND(number, decimals)

-- Round to nearest integer
SELECT ROUND(4.7);
-- Result: 5

SELECT ROUND(4.3);
-- Result: 4

-- Round to 2 decimal places
SELECT ROUND(123.4567, 2);
-- Result: 123.46

-- Round to 1 decimal place
SELECT ROUND(123.4567, 1);
-- Result: 123.5

-- Round to nearest 10 (negative decimals)
SELECT ROUND(1234.56, -1);
-- Result: 1230

SELECT ROUND(1234.56, -2);
-- Result: 1200

-- Round prices for display
SELECT 
    product_id,
    name,
    price,
    ROUND(price, 2) AS price_rounded
FROM products;

CEIL and FLOOR - Always Up or Down

-- CEIL() - round UP to nearest integer
SELECT CEIL(4.1);
-- Result: 5

SELECT CEIL(4.9);
-- Result: 5

SELECT CEIL(-4.9);
-- Result: -4

-- FLOOR() - round DOWN to nearest integer
SELECT FLOOR(4.1);
-- Result: 4

SELECT FLOOR(4.9);
-- Result: 4

SELECT FLOOR(-4.1);
-- Result: -5

-- Calculate shipping boxes needed
SELECT 
    order_id,
    total_weight,
    CEIL(total_weight / 50) AS boxes_needed
FROM orders;
-- 75 lbs → 2 boxes, 50 lbs → 1 box, 51 lbs → 2 boxes

TRUNCATE - Cut Off Decimals

-- TRUNCATE(number, decimals) - removes decimals without rounding

SELECT TRUNCATE(123.4567, 2);
-- Result: 123.45 (not 123.46)

SELECT TRUNCATE(123.4567, 0);
-- Result: 123

SELECT TRUNCATE(123.4567, -1);
-- Result: 120

-- Difference from ROUND
SELECT ROUND(4.9, 0);      -- Result: 5
SELECT TRUNCATE(4.9, 0);   -- Result: 4

2. Basic Mathematical Operations

ABS - Absolute Value

-- ABS(number) - absolute value (removes negative sign)

SELECT ABS(-5);
-- Result: 5

SELECT ABS(5);
-- Result: 5

-- Calculate variance (absolute difference)
SELECT 
    product_id,
    expected_stock,
    actual_stock,
    ABS(expected_stock - actual_stock) AS variance
FROM inventory;

SIGN - Determine Positive/Negative/Zero

-- SIGN(number) - returns -1, 0, or 1

SELECT SIGN(-5);   -- Result: -1
SELECT SIGN(0);    -- Result: 0
SELECT SIGN(5);    -- Result: 1

-- Categorize balances
SELECT 
    account_id,
    balance,
    CASE SIGN(balance)
        WHEN -1 THEN 'Negative'
        WHEN 0 THEN 'Zero'
        WHEN 1 THEN 'Positive'
    END AS balance_status
FROM accounts;

MOD - Modulo (Remainder)

-- MOD(n, m) - remainder after division

SELECT MOD(10, 3);
-- Result: 1 (10 ÷ 3 = 3 remainder 1)

SELECT MOD(15, 4);
-- Result: 3

-- Also: n % m
SELECT 10 % 3;
-- Result: 1

-- Find even/odd numbers
SELECT 
    order_id,
    CASE MOD(order_id, 2)
        WHEN 0 THEN 'Even'
        ELSE 'Odd'
    END AS parity
FROM orders;

-- Group into buckets
SELECT 
    customer_id,
    MOD(customer_id, 10) AS bucket
FROM customers;

3. Powers, Roots, and Logarithms

POW and POWER - Exponentiation

-- POW(base, exponent) or POWER(base, exponent)

SELECT POW(2, 3);
-- Result: 8 (2³)

SELECT POW(10, 2);
-- Result: 100 (10²)

-- Also: base ^ exponent (PostgreSQL syntax, not MySQL)
-- MySQL doesn't support ^ operator

-- Calculate compound interest
SELECT 
    principal * POW(1 + interest_rate, years) AS future_value
FROM investments;

SQRT - Square Root

-- SQRT(number) - square root

SELECT SQRT(16);
-- Result: 4

SELECT SQRT(2);
-- Result: 1.4142135623730951

-- Calculate distance (Pythagorean theorem)
SELECT 
    SQRT(POW(x2 - x1, 2) + POW(y2 - y1, 2)) AS distance
FROM coordinates;

EXP and LOG - Exponential and Logarithm

-- EXP(x) - e^x (natural exponential)
SELECT EXP(1);
-- Result: 2.718281828459045 (e)

-- LOG(x) - natural logarithm (base e)
SELECT LOG(2.718281828459045);
-- Result: 1

-- LOG10(x) - base 10 logarithm
SELECT LOG10(100);
-- Result: 2 (10² = 100)

-- LOG(base, x) - logarithm with custom base
SELECT LOG(2, 8);
-- Result: 3 (2³ = 8)

4. RAND - Random Numbers

RAND Function

-- RAND() - returns random float between 0.0 and 1.0

SELECT RAND();
-- Result: 0.7392837465 (random each time)

-- Random integer between 1 and 100
SELECT FLOOR(RAND() * 100) + 1;

-- Random integer between min and max
SELECT FLOOR(RAND() * (max - min + 1)) + min;

-- Select 10 random products
SELECT * FROM products
ORDER BY RAND()
LIMIT 10;

-- Random sample of customers (5%)
SELECT * FROM customers
WHERE RAND() < 0.05;

Seeded Random

-- RAND(seed) - repeatable random sequence

SELECT RAND(42);
-- Always returns same value: 0.6570213219

-- Same seed produces same sequence
SELECT RAND(100);  -- Always same result
SELECT RAND(100);  -- Same result again

5. Trigonometric Functions

Basic Trig Functions

-- All angles in radians

-- SIN(x) - sine
SELECT SIN(0);     -- Result: 0
SELECT SIN(PI());  -- Result: 0

-- COS(x) - cosine
SELECT COS(0);     -- Result: 1
SELECT COS(PI());  -- Result: -1

-- TAN(x) - tangent
SELECT TAN(0);     -- Result: 0

-- PI() - π constant
SELECT PI();
-- Result: 3.141592653589793

-- Convert degrees to radians
SELECT RADIANS(180);
-- Result: 3.141592653589793 (π)

-- Convert radians to degrees
SELECT DEGREES(PI());
-- Result: 180

6. FORMAT - Number Formatting

FORMAT Function

-- FORMAT(number, decimals) - adds thousands separators

SELECT FORMAT(1234567.89, 2);
-- Result: '1,234,567.89'

SELECT FORMAT(1234567.89, 0);
-- Result: '1,234,568'

-- Format currency for display
SELECT 
    product_id,
    name,
    CONCAT('$', FORMAT(price, 2)) AS price_display
FROM products;
-- Result: '$1,299.99'

-- Format large numbers
SELECT 
    CONCAT(FORMAT(revenue / 1000000, 1), 'M') AS revenue_millions
FROM sales_totals;
-- Result: '45.6M' for 45,600,000

7. CAST and CONVERT

CAST - Standard SQL Type Conversion

-- CAST(value AS type)

-- String to integer
SELECT CAST('123' AS SIGNED);
-- Result: 123

-- String to decimal
SELECT CAST('123.45' AS DECIMAL(10,2));
-- Result: 123.45

-- Integer to string
SELECT CAST(123 AS CHAR);
-- Result: '123'

-- Date to string
SELECT CAST('2024-12-18' AS DATE);
-- Result: 2024-12-18

-- Common types:
-- SIGNED / UNSIGNED - integers
-- DECIMAL(m,d) - decimal numbers
-- CHAR / VARCHAR - strings
-- DATE / DATETIME / TIME - dates/times
-- BINARY - binary data

CONVERT - MySQL Type Conversion

-- CONVERT(value, type) - MySQL syntax

SELECT CONVERT('123', SIGNED);
-- Result: 123

SELECT CONVERT('123.45', DECIMAL(10,2));
-- Result: 123.45

-- String to decimal for calculations
SELECT 
    product_id,
    price_string,
    CAST(price_string AS DECIMAL(10,2)) * 1.08 AS price_with_tax
FROM temp_imports;

8. Handling NULL Values

IFNULL - Replace NULL

-- IFNULL(value, replacement) - returns replacement if value is NULL

SELECT IFNULL(NULL, 0);
-- Result: 0

SELECT IFNULL(5, 0);
-- Result: 5

-- Replace NULL with default
SELECT 
    product_id,
    name,
    IFNULL(discount, 0) AS discount,
    price * (1 - IFNULL(discount, 0)) AS final_price
FROM products;

COALESCE - First Non-NULL

-- COALESCE(val1, val2, val3, ...) - returns first non-NULL value

SELECT COALESCE(NULL, NULL, 5, 10);
-- Result: 5

SELECT COALESCE(NULL, 'default');
-- Result: 'default'

-- Try multiple fallback values
SELECT 
    COALESCE(mobile_phone, home_phone, work_phone, 'No phone') AS contact_phone
FROM customers;

-- Handle multiple NULL columns
SELECT 
    COALESCE(first_name, '') AS first,
    COALESCE(middle_name, '') AS middle,
    COALESCE(last_name, '') AS last
FROM customers;

NULLIF - Convert Value to NULL

-- NULLIF(val1, val2) - returns NULL if values are equal

SELECT NULLIF(5, 5);
-- Result: NULL

SELECT NULLIF(5, 10);
-- Result: 5

-- Avoid division by zero
SELECT 
    total_sales / NULLIF(total_orders, 0) AS avg_order_value
FROM sales_summary;
-- Returns NULL instead of error when total_orders = 0

-- Convert empty strings to NULL
SELECT 
    NULLIF(email, '') AS email_clean
FROM customers;

9. Conditional Logic

IF Function

-- IF(condition, true_value, false_value)

SELECT IF(5 > 3, 'Yes', 'No');
-- Result: 'Yes'

-- Simple conditional
SELECT 
    product_id,
    stock,
    IF(stock > 0, 'In Stock', 'Out of Stock') AS availability
FROM products;

-- Conditional calculation
SELECT 
    order_id,
    total,
    IF(total >= 100, total * 0.9, total) AS discounted_total
FROM orders;

CASE Expression (Review)

-- CASE for multiple conditions

SELECT 
    product_id,
    stock,
    CASE
        WHEN stock = 0 THEN 'Out of Stock'
        WHEN stock < 10 THEN 'Low Stock'
        WHEN stock < 100 THEN 'In Stock'
        ELSE 'High Stock'
    END AS stock_status
FROM products;

10. Practical Calculation Patterns

Percentage Calculations

-- Calculate percentage
SELECT 
    category,
    sales,
    ROUND(sales / total_sales * 100, 2) AS sales_percentage
FROM category_sales;

-- Percentage change
SELECT 
    product_id,
    old_price,
    new_price,
    ROUND((new_price - old_price) / old_price * 100, 2) AS price_change_pct
FROM price_changes;

-- Completion percentage
SELECT 
    project_id,
    tasks_completed,
    total_tasks,
    ROUND(tasks_completed / NULLIF(total_tasks, 0) * 100, 1) AS completion_pct
FROM projects;

Tax and Discount Calculations

-- Calculate tax
SELECT 
    order_id,
    subtotal,
    ROUND(subtotal * 0.0825, 2) AS tax,
    ROUND(subtotal * 1.0825, 2) AS total
FROM orders;

-- Apply tiered discounts
SELECT 
    customer_id,
    order_total,
    CASE
        WHEN order_total >= 1000 THEN ROUND(order_total * 0.20, 2)
        WHEN order_total >= 500 THEN ROUND(order_total * 0.15, 2)
        WHEN order_total >= 100 THEN ROUND(order_total * 0.10, 2)
        ELSE 0
    END AS discount_amount
FROM orders;

Statistical Calculations

-- Calculate average deviation
SELECT 
    product_id,
    price,
    avg_price,
    ROUND(ABS(price - avg_price), 2) AS deviation,
    ROUND((price - avg_price) / avg_price * 100, 2) AS deviation_pct
FROM (
    SELECT 
        product_id,
        price,
        AVG(price) OVER () AS avg_price
    FROM products
) AS calc;

-- Z-score calculation
SELECT 
    product_id,
    price,
    ROUND((price - avg_price) / NULLIF(stddev_price, 0), 2) AS z_score
FROM (
    SELECT 
        product_id,
        price,
        AVG(price) OVER () AS avg_price,
        STDDEV(price) OVER () AS stddev_price
    FROM products
) AS stats;

11. 🎯 Practice Exercises

products (product_id, name, price, cost, discount_pct, stock)
orders (order_id, subtotal, tax_rate, shipping)

-- Task 1: Profit margin
SELECT 
    product_id,
    name,
    price,
    cost,
    price - cost AS profit,
    ROUND((price - cost) / NULLIF(price, 0) * 100, 2) AS profit_margin_pct
FROM products
ORDER BY profit_margin_pct DESC;


-- Task 2: Final price after discount
SELECT 
    product_id,
    name,
    price,
    IFNULL(discount_pct, 0) AS discount,
    ROUND(price * (1 - IFNULL(discount_pct, 0) / 100), 2) AS final_price
FROM products;


-- Task 3: Order totals
SELECT 
    order_id,
    subtotal,
    ROUND(subtotal * tax_rate, 2) AS tax,
    shipping,
    ROUND(subtotal * (1 + tax_rate) + shipping, 2) AS total
FROM orders;


-- Task 4: Formatted prices
SELECT 
    product_id,
    name,
    CONCAT('$', FORMAT(price, 2)) AS price_display,
    CONCAT('$', FORMAT(
        ROUND(price * (1 - IFNULL(discount_pct, 0) / 100), 2),
        2
    )) AS sale_price_display
FROM products;

sales (sale_id, amount, target, region)
employees (employee_id, name, quota, actual_sales)

-- Task 1: Percentage of target
SELECT 
    region,
    amount,
    target,
    ROUND(amount / NULLIF(target, 0) * 100, 1) AS target_pct
FROM sales;


-- Task 2: Variance from target
SELECT 
    region,
    amount,
    target,
    amount - target AS variance,
    ROUND((amount - target) / NULLIF(target, 0) * 100, 1) AS variance_pct
FROM sales
ORDER BY variance DESC;


-- Task 3: Performance categorization
SELECT 
    region,
    amount,
    target,
    CASE
        WHEN amount >= target * 1.1 THEN 'Above Target'
        WHEN amount >= target * 0.9 THEN 'At Target'
        ELSE 'Below Target'
    END AS performance
FROM sales;


-- Task 4: Commission calculation
SELECT 
    employee_id,
    name,
    quota,
    actual_sales,
    ROUND(
        actual_sales * 0.05 +
        IF(actual_sales > quota, (actual_sales - quota) * 0.02, 0),
        2
    ) AS commission
FROM employees;

products (product_id, name, price, cost, stock)
orders (order_id, product_id, quantity, order_date)
targets (month, revenue_target, profit_target)

-- Comprehensive financial dashboard
SELECT 
    p.product_id,
    p.name,
    
    -- Pricing
    CONCAT('$', FORMAT(p.price, 2)) AS price_display,
    CONCAT('$', FORMAT(p.cost, 2)) AS cost_display,
    
    -- Profit metrics
    ROUND(p.price - p.cost, 2) AS profit_per_unit,
    ROUND((p.price - p.cost) / NULLIF(p.price, 0) * 100, 1) AS margin_pct,
    
    -- Inventory value
    p.stock,
    CONCAT('$', FORMAT(p.stock * p.price, 2)) AS inventory_value_retail,
    CONCAT('$', FORMAT(p.stock * p.cost, 2)) AS inventory_value_cost,
    
    -- Sales metrics
    COALESCE(SUM(o.quantity), 0) AS units_sold,
    CONCAT('$', FORMAT(COALESCE(SUM(o.quantity * p.price), 0), 2)) AS total_revenue,
    CONCAT('$', FORMAT(
        COALESCE(SUM(o.quantity * (p.price - p.cost)), 0),
        2
    )) AS total_profit,
    
    -- Performance indicators
    CASE
        WHEN p.stock = 0 THEN '❌ Out of Stock'
        WHEN p.stock < 10 THEN '⚠️ Low Stock'
        WHEN COALESCE(SUM(o.quantity), 0) = 0 THEN '📊 No Sales'
        WHEN ROUND((p.price - p.cost) / NULLIF(p.price, 0) * 100, 1) > 50 
            THEN '⭐ High Margin'
        ELSE '✅ Normal'
    END AS status,
    
    -- Stock turns (sales velocity)
    ROUND(
        COALESCE(SUM(o.quantity), 0) / NULLIF(p.stock, 0),
        2
    ) AS stock_turn_ratio,
    
    -- Profitability score (0-100)
    LEAST(100, ROUND(
        (ROUND((p.price - p.cost) / NULLIF(p.price, 0) * 100, 1) * 0.4) +
        (LEAST(100, COALESCE(SUM(o.quantity), 0)) * 0.3) +
        (LEAST(100, p.stock / 10) * 0.3),
        0
    )) AS profitability_score

FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
    AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY 
    p.product_id, p.name, p.price, p.cost, p.stock
ORDER BY profitability_score DESC;


-- Monthly target achievement
SELECT 
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    
    -- Actual performance
    CONCAT('$', FORMAT(SUM(p.price * o.quantity), 0)) AS revenue,
    CONCAT('$', FORMAT(
        SUM((p.price - p.cost) * o.quantity),
        0
    )) AS profit,
    
    -- Targets
    CONCAT('$', FORMAT(t.revenue_target, 0)) AS revenue_target,
    CONCAT('$', FORMAT(t.profit_target, 0)) AS profit_target,
    
    -- Achievement percentages
    CONCAT(
        ROUND(SUM(p.price * o.quantity) / NULLIF(t.revenue_target, 0) * 100, 1),
        '%'
    ) AS revenue_achievement,
    CONCAT(
        ROUND(
            SUM((p.price - p.cost) * o.quantity) / NULLIF(t.profit_target, 0) * 100,
            1
        ),
        '%'
    ) AS profit_achievement,
    
    -- Status
    CASE
        WHEN SUM(p.price * o.quantity) >= t.revenue_target THEN '✅ Target Met'
        WHEN SUM(p.price * o.quantity) >= t.revenue_target * 0.9 THEN '⚠️ Close'
        ELSE '❌ Below Target'
    END AS status

FROM orders o
INNER JOIN products p ON o.product_id = p.product_id
LEFT JOIN targets t ON DATE_FORMAT(o.order_date, '%Y-%m') = t.month
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m'), t.revenue_target, t.profit_target
ORDER BY month DESC;

📝 Key Takeaways

• ROUND rounds to specified decimals; CEIL/FLOOR always round up/down
• TRUNCATE cuts decimals without rounding
• ABS returns absolute value; SIGN returns -1/0/1
• MOD returns remainder after division
• POW/POWER calculates powers; SQRT calculates square root
• RAND() generates random numbers (0.0 to 1.0)
• FORMAT adds thousands separators to numbers
• CAST/CONVERT transform data types
• IFNULL replaces NULL with default; COALESCE returns first non-NULL
• NULLIF converts values to NULL (useful for division by zero)
• IF provides simple conditional logic
• Math functions essential for calculations and analytics
• Always use NULLIF for division to prevent errors
• Combine functions for complex business logic
• These functions power real financial dashboards