SQL Built-In Numeric Functions

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100),
    category      VARCHAR(50),
    price         DECIMAL(10,2),
    cost          DECIMAL(10,2),
    stock_qty     INT
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    order_date    DATE,
    subtotal      DECIMAL(10,2),
    tax_rate      DECIMAL(5,4),
    discount_pct  DECIMAL(5,2)
);

INSERT INTO products VALUES
(101, 'Wireless Mouse',      'Electronics', 25.99,  12.50, 150),
(102, 'USB-C Cable',          'Electronics',  9.99,   3.75, 300),
(103, 'Notebook A5',          'Stationery',   4.50,   1.80, 500),
(104, 'Mechanical Keyboard',  'Electronics', 89.99,  42.00,  45),
(105, 'Desk Lamp',            'Furniture',   34.50,  18.25,   0),
(106, 'Pen Pack',             'Stationery',   2.99,   0.95, 800),
(107, 'Standing Desk',        'Furniture',  299.99, 155.00,  12);

INSERT INTO orders VALUES
(1001, 1, '2024-06-01', 155.97, 0.0875, 10.00),
(1002, 2, '2024-06-03', 299.99, 0.0625,  0.00),
(1003, 1, '2024-06-05',  45.48, 0.0875,  5.00),
(1004, 3, '2024-06-07',  89.99, 0.1000, 15.00),
(1005, 2, '2024-06-10',   9.99, 0.0625,  0.00);

SELECT
    product_name,
    price,
    cost,
    price - cost AS profit,
    (price - cost) / price * 100 AS margin_pct
FROM products;

SELECT
    product_name,
    price,
    stock_qty,
    price * stock_qty AS inventory_value
FROM products
ORDER BY inventory_value DESC;

SELECT
    order_id,
    subtotal,
    discount_pct,
    subtotal * (1 - discount_pct / 100) AS after_discount,
    subtotal * (1 - discount_pct / 100) * (1 + tax_rate) AS final_total
FROM orders;

-- Integer division: result is truncated to integer in some databases
SELECT 10 / 3 AS result;

SELECT 100 / 0;
-- ERROR: division by zero

-- Use NULLIF to turn 0 into NULL (division by NULL returns NULL, not error)
SELECT
    product_name,
    price / NULLIF(stock_qty, 0) AS price_per_unit_in_stock
FROM products;

ROUND(number, decimal_places)
ROUND(number)   -- rounds to nearest integer (0 decimal places)

SELECT
    ROUND(3.14159, 2) AS two_places,
    ROUND(3.14159, 0) AS zero_places,
    ROUND(3.14159)    AS no_arg,
    ROUND(3.5)        AS half_up,
    ROUND(2.5)        AS half_up2,
    ROUND(-3.7)       AS negative;

SELECT
    order_id,
    subtotal,
    discount_pct,
    tax_rate,
    ROUND(subtotal * (1 - discount_pct / 100) * (1 + tax_rate), 2) AS final_total
FROM orders;

SELECT
    ROUND(1234.56, -1) AS tens,
    ROUND(1234.56, -2) AS hundreds,
    ROUND(1234.56, -3) AS thousands;

-- PostgreSQL / Oracle
SELECT TRUNC(3.789, 1) AS truncated;
-- Result: 3.7 (not 3.8)

-- MySQL
SELECT TRUNCATE(3.789, 1) AS truncated;
-- Result: 3.7

-- Comparison
SELECT
    ROUND(3.789, 1) AS rounded,     -- 3.8
    TRUNC(3.789, 1) AS truncated;   -- 3.7

CEIL(number)     -- PostgreSQL, MySQL, Oracle
CEILING(number)  -- SQL Server (also supported by PostgreSQL and MySQL)
FLOOR(number)

SELECT
    CEIL(3.2)   AS ceil_positive,
    CEIL(3.8)   AS ceil_high,
    CEIL(-3.2)  AS ceil_negative,
    FLOOR(3.8)  AS floor_positive,
    FLOOR(3.2)  AS floor_low,
    FLOOR(-3.2) AS floor_negative;

-- Each box holds 25 items. How many boxes per product?
SELECT
    product_name,
    stock_qty,
    CEIL(stock_qty / 25.0) AS boxes_needed
FROM products
WHERE stock_qty > 0;

-- 7 products, 3 per page
SELECT CEIL(COUNT(*) / 3.0) AS total_pages FROM products;
-- Result: 3 (pages 1-3, with the last page having only 1 item)

-- Group products by $10 price tiers
SELECT
    product_name,
    price,
    FLOOR(price / 10) * 10 AS price_tier
FROM products
ORDER BY price_tier;

SELECT
    value,
    FLOOR(value)   AS floor_val,
    CEIL(value)    AS ceil_val,
    ROUND(value)   AS round_val
FROM (VALUES (2.3), (2.5), (2.7), (-2.3), (-2.5), (-2.7))
    AS t(value);

ABS(number)

SELECT
    ABS(42)    AS positive,
    ABS(-42)   AS negative,
    ABS(0)     AS zero,
    ABS(-3.14) AS decimal_neg;

-- Products closest to $30
SELECT
    product_name,
    price,
    ABS(price - 30) AS distance_from_30
FROM products
ORDER BY distance_from_30
LIMIT 3;

SELECT
    product_name,
    price,
    ROUND(price - AVG(price) OVER(), 2) AS diff_from_avg,
    ROUND(ABS(price - AVG(price) OVER()), 2) AS abs_diff
FROM products;

-- Function syntax (all databases)
MOD(dividend, divisor)

-- Operator syntax (PostgreSQL, MySQL, SQL Server, SQLite)
dividend % divisor

SELECT
    MOD(10, 3) AS mod_10_3,
    MOD(15, 5) AS mod_15_5,
    MOD(7, 2)  AS mod_7_2,
    MOD(10, 4) AS mod_10_4;

SELECT
    product_id,
    product_name,
    CASE WHEN MOD(product_id, 2) = 0 THEN 'Even' ELSE 'Odd' END AS parity
FROM products;

SELECT
    product_name,
    price,
    CASE WHEN MOD(ROW_NUMBER() OVER (ORDER BY product_name), 2) = 0
         THEN 'light' ELSE 'dark'
    END AS row_color
FROM products;

-- Distribute products into 3 groups
SELECT
    product_name,
    MOD(product_id, 3) AS group_number
FROM products;

-- Find order IDs that are multiples of 2
SELECT order_id FROM orders WHERE MOD(order_id, 2) = 0;
-- Results: 1002, 1004

POWER(base, exponent)
SQRT(number)

SELECT
    POWER(2, 3)   AS two_cubed,
    POWER(10, 2)  AS ten_squared,
    POWER(5, 0)   AS any_to_zero,
    POWER(2, 10)  AS two_to_ten,
    SQRT(144)     AS sqrt_144,
    SQRT(2)       AS sqrt_2;

-- Future value of $1000 at 5% annual interest over 10 years
SELECT
    1000 AS principal,
    0.05 AS rate,
    10 AS years,
    ROUND(1000 * POWER(1 + 0.05, 10), 2) AS future_value;

-- Geometric mean using POWER and logarithms
SELECT
    ROUND(POWER(
        EXP(SUM(LN(price))),
        1.0 / COUNT(*)
    ), 2) AS geometric_mean
FROM products;

-- Distance between (3, 4) and (7, 1)
SELECT ROUND(SQRT(POWER(7 - 3, 2) + POWER(1 - 4, 2)), 2) AS distance;
-- Result: 5.00

SIGN(number)

SELECT
    SIGN(42)   AS positive,
    SIGN(-42)  AS negative,
    SIGN(0)    AS zero;

SELECT
    product_name,
    price - cost AS profit,
    CASE SIGN(price - cost)
        WHEN 1  THEN 'Profit'
        WHEN 0  THEN 'Break Even'
        WHEN -1 THEN 'Loss'
    END AS classification
FROM products;

-- Natural logarithm (base e)
LN(number)       -- PostgreSQL, Oracle, MySQL
LOG(number)      -- SQL Server (natural log), MySQL (natural log)

-- Base-10 logarithm
LOG10(number)    -- all databases
LOG(10, number)  -- PostgreSQL, Oracle (general form)

-- Exponential (e raised to the power)
EXP(number)      -- all databases

SELECT
    LN(2.71828)   AS ln_e,
    LOG10(1000)   AS log10_thousand,
    EXP(1)        AS e_to_1,
    EXP(0)        AS e_to_0;

SELECT
    order_id,
    subtotal,
    discount_pct,
    ROUND(subtotal * discount_pct / 100, 2) AS discount_amount,
    ROUND(subtotal * (1 - discount_pct / 100), 2) AS after_discount,
    ROUND(subtotal * (1 - discount_pct / 100) * tax_rate, 2) AS tax_amount,
    ROUND(subtotal * (1 - discount_pct / 100) * (1 + tax_rate), 2) AS final_total
FROM orders;

SELECT
    product_name,
    cost,
    price,
    ROUND((price - cost) / cost * 100, 1) AS markup_pct,
    ROUND((price - cost) / price * 100, 1) AS margin_pct,
    ROUND(price / cost, 2) AS price_to_cost_ratio
FROM products
ORDER BY margin_pct DESC;

SELECT
    product_name,
    price * stock_qty AS inventory_value,
    ROUND(
        price * stock_qty / SUM(price * stock_qty) OVER() * 100,
        1
    ) AS pct_of_total
FROM products
ORDER BY inventory_value DESC;

-- Calculate discount percentage safely (handles zero subtotal)
SELECT
    order_id,
    subtotal,
    discount_pct,
    ROUND(
        COALESCE(
            discount_pct / NULLIF(subtotal, 0) * subtotal,
            0
        ), 2
    ) AS discount_amount
FROM orders;

-- PostgreSQL / SQL Server / SQLite
SELECT 1 / 3 AS result;
-- Result: 0 (not 0.333)

SELECT 5 / 2 AS result;
-- Result: 2 (not 2.5)

SELECT 1.0 / 3 AS result;   -- 0.333...
SELECT 5 / 2.0 AS result;   -- 2.5

-- Crashes the entire query
SELECT price / stock_qty FROM products;
-- ERROR: division by zero (when stock_qty = 0)

SELECT price / NULLIF(stock_qty, 0) FROM products;
-- Returns NULL instead of crashing

-- Rounding at each step loses precision
SELECT
    ROUND(subtotal * 0.10, 2) AS discount,  -- rounded
    ROUND(
        (subtotal - ROUND(subtotal * 0.10, 2)) * 0.0875,
        2
    ) AS tax  -- based on rounded discount
FROM orders WHERE order_id = 1001;

-- Calculate with full precision, round only at the end
SELECT
    ROUND(subtotal * (1 - discount_pct / 100) * (1 + tax_rate), 2) AS final_total
FROM orders WHERE order_id = 1001;

-- FLOAT arithmetic is imprecise
SELECT CAST(0.1 AS FLOAT) + CAST(0.2 AS FLOAT);
-- May return: 0.30000000000000004 instead of 0.3

-- DECIMAL arithmetic is exact
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));
-- Returns exactly: 0.30

-- Not what you might expect
SELECT 100 + 50 * 0.10;
-- Result: 105.0 (not 15.0)
-- Evaluates as: 100 + (50 * 0.10) = 100 + 5 = 105

-- Use parentheses for clarity
SELECT (100 + 50) * 0.10;
-- Result: 15.0