Number Functions

A numeric function takes number(s) in and gives one number back per row, but the value you get is decided by three things the SQL text does not show: the argument's type (integer vs decimal vs floating point), the engine's rounding mode, and, in the optimizer, whether wrapping a column in the function has just made an index unusable. Learn the functions as a decision about those three, not as a vocabulary list, and the surprises stop.

This page keeps the everyday tools (ABS, ROUND, CEIL/FLOOR, MOD, SQRT, POWER) but focuses on the four places where the same expression returns a different, correct answer on different engines or types — the places that cause real bugs in money math and slow queries in production.

The everyday functions, precisely

Function	What it does	Example	Result
ABS(x)	Magnitude, drops the sign	ABS(-10)	10
CEIL(x)	Up toward +∞	CEIL(4.3), CEIL(-4.3)	5, -4
FLOOR(x)	Down toward -∞	FLOOR(4.8), FLOOR(-4.3)	4, -5
ROUND(x,d)	Nearest, ties by mode (see below)	ROUND(9.876, 1)	9.9
TRUNC(x,d)	Chop toward zero, no rounding	TRUNC(9.876, 1), TRUNC(-9.876,1)	9.8, -9.8
MOD(a,b)	Remainder, sign follows dividend	MOD(17,5), MOD(-17,5)	2, -2
SQRT(x), POWER(a,b)	Square root; a raised to b	SQRT(25), POWER(2,3)	5, 8
SIGN(x)	-1 / 0 / +1	SIGN(-15)	-1

Notice already that FLOOR, TRUNC, and CEIL only diverge on negative inputs. For 4.3 they cluster around 4/5; for -4.3, FLOOR gives -5 (more negative), TRUNC gives -4 (toward zero), CEIL gives -4. "Round down" is ambiguous for negatives — say "toward zero" or "toward -∞" and mean it.

Pitfall 1 — ROUND's tie-breaking mode is not universal

The mechanism: when the discarded part is exactly half a unit (2.5 rounding to an integer), "nearest" is a tie, and the engine must pick a rule. There are two common rules and SQL engines do not agree:

• Round half away from zero (what most people learned in school): 2.5 → 3, -2.5 → -3.
• Round half to even, a.k.a. banker's rounding (the IEEE-754 default for floating point): pick the neighbour whose last digit is even, so 2.5 → 2 but 3.5 → 4. Over many values the up/down bias cancels, which is why finance and floating-point hardware default to it.

The trap is that a single engine can use both rules depending on the argument type. In PostgreSQL, ROUND on numeric rounds half away from zero, but ROUND on double precision rounds half to even:

-- PostgreSQL
SELECT ROUND(2.5);              -- 3   (2.5 literal is numeric -> half away from zero)
SELECT ROUND(2.5::float8);      -- 2   (double precision -> half to EVEN)
SELECT ROUND(3.5::float8);      -- 4   (nearest even is 4)
SELECT ROUND(0.5::float8);      -- 0   (nearest even is 0)

-- MySQL: exact-value (DECIMAL) literals round half away from zero
SELECT ROUND(2.5);              -- 3
SELECT ROUND(-2.5);             -- -3

Traced across engines

Rounding each value to 0 decimals. Read the "type" column — it, not the engine name alone, decides the rule:

Value	Half away from zero (MySQL exact, PG numeric, SQL Server, Oracle)	Half to even (PG double precision, IEEE-754)
0.5	1	0
1.5	2	2
2.5	3	2
3.5	4	4
-2.5	-3	-2

1.5 and 3.5 agree (their even neighbour is also the away-from-zero neighbour); 0.5, 2.5, -2.5 disagree. The SVG below traces the tie at 2.5 under both modes.

Pitfall 2 — integer division silently truncates

The mechanism: if both operands are integers, many engines do integer arithmetic and the result is an integer — the fractional part is discarded before you ever get to round it. This bites hardest in averages and percentages.

-- PostgreSQL, SQL Server, Oracle: integer / integer -> integer (truncates toward zero)
SELECT 5 / 2;               -- 2      (NOT 2.5)
SELECT 5.0 / 2;             -- 2.5    one operand is decimal -> decimal result
SELECT 5 / 2::numeric;      -- 2.5    cast one side

-- MySQL is the exception: '/' always yields a decimal
SELECT 5 / 2;               -- 2.5000
SELECT 5 DIV 2;             -- 2      DIV is MySQL's integer-division operator

So SELECT passed / total where both are integer counts returns 0 for every row where passed < total in Postgres/SQL Server/Oracle — a classic "why is my pass rate always 0%" bug. Fix by casting one side to a decimal/float before the divide, then round.

Pitfall 3 — MOD's sign, and DECIMAL precision/scale

MOD (and the % operator) take the sign of the dividend in standard SQL, MySQL, Postgres, and Oracle: MOD(-17, 5) = -2, not 3. (This differs from Python's %, which follows the divisor and gives 3.) So WHERE MOD(balance, 2) = 1 silently misses negative odd balances, because theirs is -1.

DECIMAL(p, s) means p total significant digits with s after the point — so at most p−s digits before it. DECIMAL(5,2) holds -999.99 to 999.99; inserting 1000.00 is an overflow error, not a silent truncation. And ROUND(x, d) on a decimal does not change the column's declared scale — it zeroes the digits past d but the type still carries s decimals. Choose s for the smallest unit you must represent (money in most currencies needs scale 2; some need 4 for intermediate math).

Pitfall 4 — dialect names differ (this is not portable "SQL")

Several functions the original page listed as generic SQL are MySQL-only spellings. Interviewers notice when you claim these are universal:

Task	MySQL	PostgreSQL
Truncate to N decimals	TRUNCATE(9.876, 1)	TRUNC(9.876, 1)
Random in [0,1)	RAND()	RANDOM()
Grouped/thousands formatting	FORMAT(1234567, 2) → '1,234,567.00'	to_char(1234567, 'FM9,999,999.00')
Ceiling	CEIL / CEILING	CEIL / CEILING

Also note: MySQL's FORMAT() returns a string (with commas), not a number — never wrap it around a value you still need to compute on or sort numerically.

The senior point: numeric functions can kill your index (sargability)

The mechanism: a B-tree index on a column stores the raw column values in sorted order. A predicate can use that index only if the engine can translate it into a range over those raw values — that property is called sargable (Search-ARGument-able). The moment you wrap the indexed column in a function, the engine can no longer reason about the stored order: it must compute the function for every row and test the result, which is a full scan.

-- NOT sargable: engine computes ABS(delta) for every row -> full table scan
WHERE ABS(delta) > 100

-- Sargable rewrite: two ranges over the raw column, both index-usable
WHERE delta > 100 OR delta < -100

-- NOT sargable: MOD(id, 2) must be evaluated per row
WHERE MOD(id, 2) = 0
-- Fixes: store/index a computed column  is_even  (or a generated column),
-- or accept the scan if the table is tiny.

The same rule kills WHERE ROUND(price) = 10 and WHERE price * 1.1 > 100: put the arithmetic on the constant side (price > 100 / 1.1) so the column stays bare. This is the single most interview-relevant fact on the page — a correct query that does a full scan on a billion rows is a failed design.

Selection & trade-offs

• ROUND vs TRUNC vs CAST. ROUND gives the nearest value but drags in the tie-mode ambiguity above — fine for display, risky for money unless you pin the type. TRUNC is deterministic (always toward zero, no mode) but biased low in magnitude — correct for "how many whole units fit," wrong for fair rounding. CAST(x AS DECIMAL(p,s)) rounds using the engine's mode too, so it is not a mode-free escape; its job is fixing the type, not the rounding rule.
• Round in SQL or in the app? Round in SQL when the rounded value is what you store or aggregate on (and pin an exact type). Round at the application boundary for presentation money, where you want one language's well-specified rounding mode and locale formatting rather than betting on each database's default. Rule of thumb: keep full precision through all intermediate math, round exactly once, as late as possible.
• Function on a column vs a stored/generated column. If you must filter or sort by MOD(id,10) or ROUND(price) often, materialize it as a generated/computed column and index that, trading a little write cost and storage for a sargable read. One-off analytics? Just take the scan.

Takeaways

• ROUND's tie rule is not universal — half-away-from-zero vs half-to-even — and can differ by type within one engine (Postgres numeric vs double precision). For money, pin an exact type and round once, or round in the app.
• Integer / integer truncates in Postgres, SQL Server, and Oracle (and via DIV in MySQL); cast one side to decimal before dividing.
• MOD takes the sign of the dividend (MOD(-17,5) = -2); FLOOR/TRUNC/CEIL only differ on negatives; DECIMAL(p,s) overflows loudly.
• Wrapping an indexed column in any numeric function makes the predicate non-sargable — rewrite as a range on the bare column, or index a generated column.

---

Sources: PostgreSQL and MySQL reference manuals (mathematical functions, numeric types, and rounding behavior); IEEE 754 round-half-to-even; SQL Server / Oracle ROUND semantics; standard sargability guidance from query-optimization literature. Re-authored/Deepened for this guide.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — Number Functions

Why this exists / the decision it encodes

Numeric functions are not a vocabulary quiz — they are decisions about type, rounding mode, and whether wrapping a column kills an index. Money bugs and silent 0% rates come from integer division and engine-dependent ROUND ties; production latency comes from ABS(col)/ROUND(col) predicates.

Worked example with numbers or traced SQL/FD

-- Postgres ROUND mode by type:
ROUND(2.5)           → 3  (numeric, half away from zero)
ROUND(2.5::float8)   → 2  (half to even)
-- Integer division:
SELECT 5/2;          → 2 in PG/SQL Server/Oracle (not 2.5)
SELECT 5.0/2;        → 2.5
-- MOD sign of dividend: MOD(-17,5) = -2 (not 3 like Python %)
-- Sargable rewrite:
WHERE ABS(delta) > 100     -- full scan
WHERE delta > 100 OR delta < -100  -- indexable ranges

When NOT / named alternative

Do not ROUND money intermediates every step — keep full precision, round once late. Do not filter with MOD(id,2)=0 on huge tables without a generated/indexed column. Prefer app-layer presentation rounding when locale/display rules dominate.

Failure mode / ops fingerprint / interview trap

Trap: pass_rate = passed/total always 0 for passed<total with integer columns. Ops: billing off-by-one cent between services using float ROUND vs numeric. Interview: claim ROUND is universal — refute with type-dependent modes.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: sargability of numeric expressions is query judgment. Exact types (DECIMAL) are schema integrity for money, not cosmetic.

Hostile-panel drills (with model answers)

Q1. Why does ROUND(2.5) differ from ROUND(2.5::float8) in Postgres?
Model answer: numeric uses half-away-from-zero; double precision follows IEEE half-to-even. Type, not just engine name, selects the mode.

Q2. How do you get a true ratio of integer counts in Postgres?
Model answer: Cast at least one operand before divide: passed::numeric / total, then ROUND once.

Q3. Make WHERE ROUND(price)=10 sargable in spirit.
Model answer: Prefer a range on bare price that covers values that round to 10, or store/index a generated rounded column if you must equality-filter the rounded form constantly.

I just read a lesson on **Number Functions** (Databases) and want to truly understand it. Explain Number Functions from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Number Functions** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Number Functions** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Number Functions** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.