Numeric Functions

Scalar Numeric Functions

Numeric functions transform a single numeric value per row — rounding, truncating, taking absolute values, or computing remainders.

ROUND — Rounding to N Decimal Places

sql
SELECT ROUND(316.6666, 2) AS rounded;
-- 316.67

SELECT ROUND(amount, 0) FROM orders WHERE customer_name = 'Charlie';
-- 150   (150.00 rounded to 0 decimal places)

Signature: ROUND(number, decimal_places). A negative decimal_places rounds to the left of the decimal point: ROUND(1234, -2) → 1200.

CEILING / CEIL — Round Up

sql
SELECT CEILING(150.10) AS ceil_val;
-- 151

SELECT CEIL(150.00) AS ceil_val;
-- 150   (already a whole number, unchanged)

CEILING() and CEIL() are synonyms in most dialects (MySQL and PostgreSQL support both; some engines prefer one spelling).

FLOOR — Round Down

sql
SELECT FLOOR(150.90) AS floor_val;
-- 150

FLOOR always rounds toward negative infinity: FLOOR(-150.10) is -151, not -150 (contrast with simple truncation).

ABS — Absolute Value

sql
SELECT ABS(-250.00) AS abs_val;
-- 250.00

SELECT ABS(amount - 300) AS distance_from_300 FROM orders;
-- for amount=250: |250-300| = 50.00
-- for amount=400: |400-300| = 100.00

ABS is commonly used to compute a magnitude of difference regardless of direction (e.g. "how far off was the estimate?").

MOD / % — Remainder

sql
SELECT MOD(17, 5) AS remainder;   -- 2   (standard function form)
SELECT 17 % 5 AS remainder;       -- 2   (operator form, most dialects)

Dialect variance: MOD(a, b) is widely supported (MySQL, Oracle, PostgreSQL); the % operator works in MySQL, SQL Server, and PostgreSQL. Standard SQL itself doesn't mandate one universal spelling, so check your engine.

MOD is frequently used to test even/odd (id % 2 = 0) or to bucket rows into a fixed number of groups (id % 3 gives values 0, 1, 2 in rotation).

Integer vs Decimal Division — Cross-Reference

As covered in Chapter 8 (Data Types), dividing two INT operands with / in some dialects (e.g. MySQL for INT/INT, or explicit integer types in others) can perform integer division, discarding the remainder (7 / 2 → 3, not 3.5). This is distinct from MOD, which specifically returns that discarded remainder (7 % 2 → 1). When you need a true decimal result, cast at least one operand: 7 / 2.0 → 3.5.

Edge Cases

• ROUND() with a NULL input returns NULL.
• MOD(x, 0) (or x % 0) raises a division-by-zero error in most dialects, exactly like / by zero.
• Rounding behavior at exact .5 boundaries (e.g. ROUND(2.5, 0)) can differ subtly between "round half up" and "round half to even" (banker's rounding) depending on the engine and data type — worth verifying if precision at the boundary matters.

Key Takeaways / Interview Q&A

Q: What's the difference between CEILING and FLOOR? A: CEILING rounds up to the next whole number (or leaves it unchanged if already whole); FLOOR rounds down toward negative infinity.

Q: What does MOD(17, 5) or 17 % 5 return? A: 2 — the remainder after dividing 17 by 5 three times (15), leaving 2.

Q: What's the difference between integer division and MOD? A: Integer division (/ on two integer operands in some dialects) discards the remainder and keeps the quotient; MOD (%) does the opposite — it discards the quotient and returns just the remainder.

Q: Does ROUND(x, -2) make sense? A: Yes — a negative decimal-places argument rounds to the left of the decimal point, e.g. ROUND(1234, -2) = 1200.