ROUND & Numeric Functions
SQL provides built-in numeric functions for rounding, absolute values, ceiling, floor, and math. These are essential for clean financial reports, percentage calculations, and data formatting.
Numeric Functions
- ROUND(n, d) — round to d decimal places: ROUND(99.567, 2) = 99.57
- FLOOR(n) — round down: FLOOR(9.9) = 9
- CEIL/CEILING(n) — round up: CEIL(9.1) = 10
- ABS(n) — absolute value: ABS(-5) = 5
- MOD(a, b) — remainder: MOD(10, 3) = 1
- POWER(base, exp) — exponent: POWER(2, 10) = 1024
- SQRT(n) — square root: SQRT(16) = 4
- TRUNCATE(n, d) / TRUNC(n, d) — truncate (no rounding)
Numeric Function Examples
-- Round average order value to 2 decimal places
SELECT ROUND(AVG(total_amount), 2) AS avg_order FROM orders;
-- Revenue summary with proper rounding
SELECT
ROUND(SUM(amount), 2) AS total_revenue,
ROUND(AVG(amount), 2) AS avg_payment,
ROUND(MAX(amount) - MIN(amount), 2) AS price_range
FROM payments;
-- Discount percentage (avoid division by zero)
SELECT
name,
price,
499.99 AS sale_price,
ROUND((price - 499.99) / NULLIF(price, 0) * 100, 1) AS discount_pct
FROM products WHERE price > 499.99;
-- FLOOR and CEIL
SELECT
FLOOR(price) AS floor_price,
CEIL(price) AS ceil_price,
ROUND(price) AS rounded_price
FROM products;
-- ABS: absolute difference from target price
SELECT name, ABS(price - 500) AS distance_from_500
FROM products
ORDER BY distance_from_500;
-- MOD: alternate rows (even/odd IDs)
SELECT id, name FROM products WHERE MOD(id, 2) = 0; -- even IDsQuick Quiz
Tip
Tip
Practice ROUND Numeric 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 ROUND Numeric 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 ROUND Numeric 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 provides built-in numeric functions for rounding, absolute values, ceiling, floor, and math.
- ROUND(n, d) — round to d decimal places: ROUND(99.567, 2) = 99.57
- FLOOR(n) — round down: FLOOR(9.9) = 9
- CEIL/CEILING(n) — round up: CEIL(9.1) = 10