Mathematical Functions
SQL math functions handle rounding, percentages, statistics, and financial calculations. Combined with aggregates and window functions, they power complex analytics.
Math Functions Reference
- ROUND(n, d) - round to d decimal places
- FLOOR(n) - round down to integer
- CEIL(n) - round up to integer
- ABS(n) - absolute value
- MOD(a, b) - remainder (% in some dialects)
- POWER(base, exp) - exponentiation
- SQRT(n) - square root
- RANDOM() / RAND() - random number 0-1 (PostgreSQL/MySQL)
- PI() - pi constant
- GREATEST(a, b) / LEAST(a, b) - maximum/minimum of values
Math Function Examples
-- Revenue % of total (percentage calculation)
SELECT
payment_method,
SUM(amount) AS revenue,
ROUND(
SUM(amount) * 100.0 / (SELECT SUM(amount) FROM payments),
2
) AS pct_of_total
FROM payments
GROUP BY payment_method;
-- Price after discount with rounding
SELECT
name,
price,
ROUND(price * 0.85, 2) AS discounted_price, -- 15% off
ROUND(price * 0.15, 2) AS discount_amount
FROM products;
-- Random sample of products (for A/B testing etc.)
SELECT name, price FROM products ORDER BY RANDOM() LIMIT 2; -- PostgreSQL
-- MySQL: ORDER BY RAND() LIMIT 2
-- GREATEST / LEAST for clamping values
SELECT name, GREATEST(price, 100) AS min_100_price FROM products;
-- Ensures price is never less than 100
-- Compound interest formula: P × (1 + r)^n
SELECT ROUND(999.99 * POWER(1.05, 3), 2) AS future_value;
-- $999.99 at 5% for 3 years = $1157.62Quick Quiz
Tip
Tip
Practice Mathematical Functions in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Window functions = aggregate without GROUP BY. Calculate across related rows.
Common Mistake
Warning
A common mistake with Mathematical Functions is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Mathematical Functions from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- SQL math functions handle rounding, percentages, statistics, and financial calculations.
- ROUND(n, d) - round to d decimal places
- FLOOR(n) - round down to integer
- CEIL(n) - round up to integer