Numeric Functions

Last Updated: June 12, 2026 • 16 min read

SQL provides a comprehensive set of numeric (mathematical) functions that allow you to perform calculations, round values, generate random numbers, and transform numeric data directly in your queries. These functions are essential for financial reporting, statistical analysis, data bucketing, and any scenario where raw numbers need to be shaped for presentation or downstream logic.

1. Introduction

Numeric functions handle rounding (ROUND, CEIL, FLOOR, TRUNC), arithmetic (ABS, MOD, POWER, SQRT), and utility operations (RANDOM, SIGN). They're the backbone of financial calculations, rating displays, data bucketing, and pagination math. Understanding the subtle differences between rounding modes — especially with negative numbers — is critical for correct results.

2. Why It Matters

Financial accuracy: Tax, discounts, and margins need correct rounding to cents — a 1-cent error across millions of transactions is a real problem.
Display formatting: Showing "4.3 stars" instead of "4.285714" requires ROUND for readability.
Data bucketing: Grouping ages into brackets (20-29, 30-39) uses FLOOR-based math.
Integer division trap: 7 / 2 = 3 in SQL (integer division) — a common bug that produces wrong percentages.

3. Real-World Analogy

Numeric functions are like a cash register's rounding rules. When a cashier rounds your total to the nearest cent, that's ROUND(n, 2). When a store rounds up to the next dollar for gift wrapping fees, that's CEIL. When a budget spreadsheet drops all cents to show only whole dollars, that's TRUNC. The key insight: different rounding rules serve different purposes, and using the wrong one can cause financial discrepancies.

4. How It Works

Four rounding functions with different behaviors:

Input	ROUND(n,0)	CEIL(n)	FLOOR(n)	TRUNC(n,0)
4.7	5	5	4	4
4.3	4	5	4	4
-4.3	-4	-4	-5	-4
-4.7	-5	-4	-5	-4

ABS(n): Absolute value. ABS(-42) → 42
MOD(a, b): Remainder of a/b. MOD(17, 5) → 2
POWER(base, exp): Exponentiation. POWER(2, 10) → 1024
RANDOM(): Random float between 0 and 1. Use setseed(n) for reproducible results.

5. Internal Architecture

How the engine handles numeric operations:

6. Visual Explanation

7. Practical Example

8. Common Mistakes

Integer division: 7/2 returns 3, not 3.5. Cast at least one operand: 7.0 / 2 or 7::numeric / 2.
TRUNC vs FLOOR for negatives: TRUNC(-4.7) = -4 but FLOOR(-4.7) = -5. TRUNC chops toward zero, FLOOR goes toward negative infinity.
ROUND with floating-point: ROUND(2.675, 2) may return 2.67 on FLOAT. Cast to NUMERIC first for exact rounding.
MOD preserves sign: MOD(-7, 3) = -1 in PostgreSQL, not 2.

Interview Insight

Classic question: "Round a price to the nearest 0.05." Answer: ROUND(price / 0.05, 0) * 0.05. Also: difference between TRUNC and FLOOR for negatives.

Common Pitfall

Integer division trap: 7 / 2 returns 3, not 3.5. Similarly, AVG(integer_column) returns an integer — cast to numeric for decimal averages.

9. Quick Quiz

Q1: What does FLOOR(-4.1) return?

A) -4 B) -5 C) 4

Answer: B — FLOOR rounds toward negative infinity, so -4.1 becomes -5.

Q2: What does SELECT 10 / 3 return in PostgreSQL?

A) 3.33 B) 3 C) 3.0

Answer: B — Both operands are integers, so integer division truncates to 3.

10. Scenario-Based Challenge

Build a Pricing Engine

Your e-commerce platform needs: (1) bulk discount = 15% off, rounded to nearest cent, (2) shipping cost = CEIL(weight_kg) * $5, (3) tax = 8.75% rounded to cents, (4) display price rounded to nearest $0.25 for a "charm pricing" feature. Write a single query computing all four values for a product with price=$49.99 and weight=2.3kg. Beware the integer division trap when computing per-unit prices.

11. Debugging Exercise

This completion-rate query returns 0 for all groups. What's wrong?

12. Interview Questions

Q: What's the difference between ROUND, CEIL, FLOOR, and TRUNC?

A: ROUND goes to the nearest value (ties away from zero). CEIL always rounds up (toward +∞). FLOOR always rounds down (toward -∞). TRUNC simply chops off digits (toward zero). For positives, TRUNC = FLOOR. For negatives, TRUNC = CEIL.

Q: Why might ROUND(2.675, 2) return 2.67 instead of 2.68?

A: IEEE 754 floating-point can't represent 2.675 exactly — it's stored as ~2.6749999... so ROUND sees a value below 2.675 and rounds down. Fix: cast to NUMERIC first, which stores exact decimals.

Q: How do you bucket continuous values into ranges?

A: Use FLOOR: FLOOR(age / 10.0) * 10 creates buckets like 20, 30, 40. Combine with GROUP BY for counts per bucket. Use WIDTH_BUCKET() for equal-width histogram bins.

13. Production Considerations

Use NUMERIC for money: Never use FLOAT/REAL for financial calculations — floating-point imprecision causes rounding errors. Use NUMERIC(precision, scale) columns and cast before ROUND.
RANDOM() is approximate: WHERE RANDOM() < 0.1 gives approximately 10%, not exactly. For exact sampling, use ORDER BY RANDOM() LIMIT n.
TABLESAMPLE for large tables: TABLESAMPLE BERNOULLI(10) is faster than WHERE RANDOM() < 0.1 on millions of rows.
Reproducible testing: Call setseed(0.42) before RANDOM() for deterministic results in tests and debugging.