SQL Functions

Numeric Functions

Perform mathematical operations with SQL numeric functions for rounding, aggregation, and statistical analysis.

Rounding & Precision — Control decimal places with ROUND, CEIL, and FLOOR
Statistical Analysis — Compute averages, variances, and standard deviations Mathematics powers every analytical query you write.

Rounding and Truncation

DfROUND()

Rounds a numeric value to a specified number of decimal places. The second argument controls precision — omit it to round to the nearest integer.

-- Round to different decimal places
SELECT
    123.456 AS original,
    ROUND(123.456, 2) AS two_decimals,
    ROUND(123.456, 1) AS one_decimal,
    ROUND(123.456, 0) AS nearest_integer,
    ROUND(123.456, -1) AS nearest_ten,
    ROUND(123.456, -2) AS nearest_hundred;

-- Round financial data for reporting
SELECT
    order_id,
    amount,
    ROUND(amount, 2) AS rounded_amount,
    ROUND(amount / 1.08, 2) AS pre_tax_amount
FROM orders;

DfCEIL / FLOOR

CEIL (ceiling) rounds up to the nearest integer; FLOOR rounds down. Both always return an integer.

-- CEIL rounds up, FLOOR rounds down
SELECT
    4.2 AS value,
    CEIL(4.2) AS ceiled,
    FLOOR(4.2) AS floored,
    CEIL(-4.2) AS neg_ceiled,
    FLOOR(-4.2) AS neg_floored;

DfTRUNC

Truncates a number to a specified number of decimal places without rounding. Always cuts off — never rounds up or down.

-- TRUNC removes decimals without rounding
SELECT
    9.8765 AS value,
    TRUNC(9.8765, 2) AS truncated_2,
    TRUNC(9.8765, 0) AS truncated_0,
    TRUNC(9.8765, -1) AS truncated_neg1;

Rounding Functions Comparison

Function	9.5	-9.5	9.4	-9.4	Behavior
`ROUND(x, 0)`	10	-10	9	-9	Nearest integer
`CEIL(x)`	10	-9	10	-9	Always rounds away from zero
`FLOOR(x)`	9	-10	9	-10	Always rounds toward negative
`TRUNC(x, 0)`	9	-9	9	-9	Always truncates toward zero

Absolute Value and Sign

DfABS()

Returns the absolute (non-negative) value of a number. Negative signs are removed; positive values stay unchanged.

-- ABS removes negative signs
SELECT
    -42 AS negative,
    ABS(-42) AS absolute,
    42 AS positive,
    ABS(42) AS still_positive;

-- Calculate price difference regardless of direction
SELECT
    product_name,
    old_price,
    new_price,
    ABS(new_price - old_price) AS price_change,
    CASE
        WHEN new_price > old_price THEN 'Increase'
        WHEN new_price < old_price THEN 'Decrease'
        ELSE 'No Change'
    END AS direction
FROM products;

DfSIGN()

Returns -1, 0, or 1 indicating whether a number is negative, zero, or positive.

-- Determine the sign of values
SELECT
    value,
    SIGN(value) AS sign_indicator
FROM (VALUES (-5), (-1), (0), (1), (5)) AS t(value);

Power and Root Functions

DfPOWER / SQRT

POWER(base, exponent) raises a number to a given power. SQRT(n) returns the square root. Both return NULL for negative square roots.

-- Calculate powers and roots
SELECT
    2 AS base,
    POWER(2, 3) AS cubed,
    POWER(2, 10) AS power_10,
    SQRT(144) AS square_root,
    POWER(144, 0.5) AS root_via_power;

-- Compound interest formula: A = P(1 + r/n)^(nt)
SELECT
    1000 AS principal,
    0.05 AS annual_rate,
    12 AS compounds_per_year,
    5 AS years,
    ROUND(
        1000 * POWER(1 + 0.05 / 12, 12 * 5), 2
    ) AS future_value;

Use POWER(x, 0.5) instead of SQRT(x) when you need to compute other roots (cube root: POWER(x, 1.0/3.0)).

Logarithmic Functions

DfLOG / LN

LOG(n) returns the base-10 logarithm. LN(n) returns the natural logarithm (base e). Useful for scaling data and growth rate analysis.

-- Logarithmic calculations
SELECT
    100 AS value,
    LOG(100) AS log_base10,
    LN(100) AS natural_log,
    LOG(100) / LOG(2) AS log_base2,
    EXP(1) AS euler_number;

-- Use log scale for exponential data visualization
SELECT
    product_name,
    sales_count,
    LOG(sales_count + 1) AS log_sales
FROM product_sales
ORDER BY sales_count DESC;

Modulo and Integer Division

DfMOD / %

Returns the remainder after dividing one number by another. Useful for grouping, filtering even/odd rows, and cyclic patterns.

-- Find even and odd order IDs
SELECT
    order_id,
    CASE
        WHEN order_id % 2 = 0 THEN 'Even'
        ELSE 'Odd'
    END AS parity
FROM orders;

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

DfInteger Division

Divides two integers and discards the remainder. In PostgreSQL, use div(); in MySQL and SQL Server, use FLOOR(a / b).

-- PostgreSQL: integer division
SELECT
    div(17, 3) AS quotient,
    mod(17, 3) AS remainder;

Aggregate Numeric Functions

DfSUM, AVG, MIN, MAX

Standard aggregation functions that compute totals, averages, and extremes across rows in a group.

-- Basic numeric aggregation
SELECT
    department,
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees
GROUP BY department;

Advanced Numeric Aggregates

DfVARIANCE / STDDEV

VARIANCE() measures how spread out values are from the mean. STDDEV() is the square root of variance, returning values in the same units as the data.

-- Statistical analysis of salaries
SELECT
    department,
    COUNT(*) AS count,
    ROUND(AVG(salary), 2) AS mean,
    ROUND(VARIANCE(salary), 2) AS variance,
    ROUND(STDDEV(salary), 2) AS std_dev,
    ROUND(STDDEV(salary) / AVG(salary) * 100, 2) AS coeff_of_variation
FROM employees
GROUP BY department;

VARIANCE and STDDEV return NULL if only one row exists in the group. Always check your row counts before relying on statistical measures.

Practical Examples

Discount Calculator

-- Calculate tiered discounts with precision
SELECT
    product_name,
    price,
    CASE
        WHEN price >= 100 THEN ROUND(price * 0.20, 2)
        WHEN price >= 50 THEN ROUND(price * 0.15, 2)
        ELSE ROUND(price * 0.10, 2)
    END AS discount,
    CASE
        WHEN price >= 100 THEN ROUND(price * 0.80, 2)
        WHEN price >= 50 THEN ROUND(price * 0.85, 2)
        ELSE ROUND(price * 0.90, 2)
    END AS final_price
FROM products;

Percentage of Total

-- Calculate each department's share of total company salary
SELECT
    department,
    SUM(salary) AS dept_salary,
    ROUND(
        SUM(salary) * 100.0 / SUM(SUM(salary)) OVER(), 1
    ) AS pct_of_total
FROM employees
GROUP BY department
ORDER BY pct_of_total DESC;

Running Percentage Calculation

-- Calculate month-over-month percentage growth
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    month,
    revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
        1
    ) AS growth_pct
FROM monthly;

Always divide by NULLIF(denominator, 0) to avoid division-by-zero errors. SQL returns NULL instead of throwing an error.