SQL Numeric Functions

The Complete Guide

Let’s dive into SQL numeric functions that every data professional should know. I’ll explain each one with real examples you’ll actually use.

Get Uma Mahesh Dasari’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

ROUND — Precise Number Control

-- Basic way to round numbers
SELECT ROUND(3.14159, 2);  -- 3.14

-- Financial calculations
SELECT 
    order_id,
    amount,
    ROUND(amount * tax_rate, 2) as tax_amount,
    ROUND(amount + (amount * tax_rate), 2) as total_with_tax
FROM orders;

-- Output: $156.70, $12.54, $169.24

What’s cool about ROUND?

• Perfect for financial calculations
• Controls decimal precision
• Handles negative places (e.g., ROUND(1234.56, -2) = 1200)

FLOOR and CEILING — Directional Rounding

-- Force rounding direction
SELECT FLOOR(3.7);    -- 3
SELECT CEILING(3.7);  -- 4

-- Real example: Calculate container needs
SELECT 
    product_name,
    quantity,
    items_per_box,
    CEILING(quantity / items_per_box) as boxes_needed,
    FLOOR(quantity / items_per_box) as full_boxes
FROM inventory;

When to use FLOOR/CEILING:

• Resource planning (CEILING for minimum boxes needed)
• Inventory management (FLOOR for complete units)
• Conservative vs liberal calculations

ABS — Working with Differences

-- Get positive differences
SELECT ABS(-15);  -- 15
SELECT ABS(15);   -- 15

-- Find biggest variances
SELECT 
    product_name,
    target_stock,
    current_stock,
    ABS(current_stock - target_stock) as stock_variance
FROM inventory
ORDER BY stock_variance DESC;

ABS is your friend when:

• Calculating differences regardless of direction
• Sorting by magnitude
• Finding largest deviations

MOD — Remainder Calculations

-- Get division remainder
SELECT MOD(15, 4);  -- 3 (15 divided by 4 = 3 remainder 3)

-- Practical use: Rotation assignments
SELECT 
    task_id,
    MOD(task_id, team_size) + 1 as assigned_to_team_member
FROM tasks;

MOD helps you:

• Create round-robin distributions
• Check even/odd numbers
• Handle cyclic patterns

CASE — Numeric Logic

-- Apply conditional calculations
SELECT 
    order_amount,
    CASE 
        WHEN order_amount >= 1000 THEN order_amount * 0.85  -- 15% off
        WHEN order_amount >= 500 THEN order_amount * 0.90   -- 10% off
        ELSE order_amount                                    -- No discount
    END as final_amount
FROM orders;

Perfect for:

• Tiered calculations
• Custom business logic
• Conditional transformations

NULLIF and COALESCE — NULL Handling

-- Find changed values
SELECT NULLIF(new_value, old_value) as changed_value;

-- Get first non-NULL price
SELECT 
    product_id,
    COALESCE(sale_price, regular_price, base_price) as effective_price
FROM products;

Use these when:

• Detecting changes (NULLIF)
• Setting fallback values (COALESCE)
• Cleaning data

Putting It All Together
Here’s a real-world example using multiple functions:

-- Sales analysis dashboard
SELECT 
    product_name,
    ROUND(
        CASE
            WHEN ABS(target_sales - actual_sales) <= 100 THEN
                actual_sales * 1.1  -- 10% bonus for hitting target
            ELSE
                actual_sales
        END, 
    2) as adjusted_sales,
    COALESCE(
        NULLIF(current_price, base_price),
        base_price
    ) as effective_price,
    CEILING(stock_needed / package_size) as packages_required
FROM sales_metrics;

Tips:

1. Combine functions thoughtfully
2. Consider NULL handling in calculations
3. Watch for decimal precision
4. Test with edge cases

Common Gotchas:

• Rounding with negative numbers
• Integer division truncation
• NULL propagation
• Decimal precision limits

This guide covers the essential numeric functions you’ll use daily. Master these, and you’ll handle most data transformation needs with confidence.