SQL 52 🛢️ Numeric and Math Functions
Numbers are the simplest data type in SQL, and the math functions are the simplest functions. Addition, subtraction, multiplication, and division are the operators. The functions—absolute value, rounding, ceiling, floor, power, square root, and the trigonometric and logarithmic functions—extend them. This chapter covers the numeric and math functions, the differences between integer and floating-point arithmetic, the rounding behavior that surprises developers, and the patterns that use math functions in real queries.
Key point: SQL arithmetic follows the type of its operands. Integer division truncates toward zero, so 7 / 2 is 3, not 3.5. Floating-point arithmetic preserves the fractional part, so 7.0 / 2 is 3.5. The ROUND function rounds half away from zero in most databases, but the exact behavior depends on the type and the database. The CAST function converts between types, which is how integer division is avoided.
Why math functions matter
The reporting problem. Reports are full of arithmetic: totals, averages, percentages, growth rates, and ratios. Some of these are computed with operators. Others—percentages, rounding, and absolute differences—need functions.
The precision problem. Integer division is the most common surprise in SQL arithmetic. SELECT 7 / 2 returns 3 in PostgreSQL, MySQL, and SQL Server, because both operands are integers and the result is an integer. The fix is to cast one operand to a decimal or float type. Knowing this prevents reports that are off by a factor of two.
The rounding problem. Rounding is not consistent across databases. ROUND(2.5) returns 3 in PostgreSQL and 2 in some other systems, depending on the rounding mode. The ROUND function takes a second argument for the number of decimal places, and the behavior at the midpoint depends on the type. Knowing the behavior prevents off-by-one errors in financial reports.
The null problem. Any arithmetic with NULL produces NULL. A column that contains NULL produces NULL for the sum, the average, and the product. The aggregate functions ignore NULL, but the arithmetic operators do not. The COALESCE function substitutes a value for NULL, which is the standard fix.
The type problem. The result of an arithmetic operation has a type. Integer plus integer is integer. Integer plus numeric is numeric. The type determines the precision and the rounding. Knowing the type rules is knowing what the result will be.
a. Basic arithmetic and integer division
The arithmetic operators are +, -, *, /, and % (modulo).
SELECT 7 + 2; -- 9
SELECT 7 - 2; -- 5
SELECT 7 * 2; -- 14
SELECT 7 / 2; -- 3 (integer division)
SELECT 7 % 2; -- 1 (remainder)The division result depends on the types of the operands. If both are integers, the result is an integer and the fractional part is truncated.
SELECT 7 / 2; -- 3
SELECT 7.0 / 2; -- 3.5
SELECT 7 / 2.0; -- 3.5
SELECT 7::numeric / 2; -- 3.5The ::numeric cast converts the integer to a numeric type. The division then produces a numeric result with the fractional part. The cast can be written as CAST(7 AS numeric).
The truncation is toward zero, not toward negative infinity.
SELECT -7 / 2; -- -3
SELECT -7.0 / 2; -- -3.5The % operator returns the remainder. The sign of the remainder follows the dividend in most databases.
SELECT 7 % 3; -- 1
SELECT -7 % 3; -- -1
SELECT 7 % -3; -- 1The modulo is useful for cycling through values, for computing the day of the week from a day number, and for pagination.
The arithmetic with NULL produces NULL.
SELECT 7 + NULL; -- NULL
SELECT NULL * 2; -- NULL
SELECT NULL / 0; -- NULL (not an error)The COALESCE function substitutes a value.
SELECT COALESCE(quantity, 0) * price FROM order_items;The aggregate functions—SUM, AVG, MIN, MAX, COUNT—ignore NULL. The SUM of a column with NULL values is the sum of the non-NULL values. The AVG is the average of the non-NULL values. The COUNT(column) counts the non-NULL values, and COUNT(*) counts all rows.
SELECT SUM(amount) FROM orders; -- ignores NULL
SELECT AVG(amount) FROM orders; -- ignores NULL
SELECT COUNT(amount) FROM orders; -- counts non-NULL
SELECT COUNT(*) FROM orders; -- counts all rowsb. Rounding, ceiling, and floor
The ROUND function rounds a number to a specified number of decimal places. The second argument is optional and defaults to 0.
SELECT ROUND(2.5); -- 3
SELECT ROUND(2.4); -- 2
SELECT ROUND(2.567, 2); -- 2.57
SELECT ROUND(2.567, 1); -- 2.6
SELECT ROUND(1234.5, -2); -- 1200The negative second argument rounds to the left of the decimal point. ROUND(1234.5, -2) rounds to the nearest hundred.
The rounding of the midpoint depends on the database and the type. In PostgreSQL, ROUND(2.5) returns 3 because the numeric type rounds half away from zero. In MySQL, ROUND(2.5) returns 3 for the DECIMAL type and 2 for the DOUBLE type, because the floating-point representation of 2.5 is exact but the rounding mode is different.
-- PostgreSQL
SELECT ROUND(2.5::numeric); -- 3
SELECT ROUND(2.5::float8); -- 2 or 3, depending on the representationThe CEIL and CEILING functions round up to the nearest integer. The FLOOR function rounds down.
SELECT CEIL(2.1); -- 3
SELECT CEIL(2.9); -- 3
SELECT CEIL(-2.1); -- -2
SELECT FLOOR(2.9); -- 2
SELECT FLOOR(2.1); -- 2
SELECT FLOOR(-2.1); -- -3The CEIL and FLOOR are useful for pagination, for binning values into ranges, and for computing the number of pages.
SELECT CEIL(COUNT(*) / 10.0) AS pages FROM items;The TRUNC function truncates toward zero without rounding.
SELECT TRUNC(2.9); -- 2
SELECT TRUNC(2.1); -- 2
SELECT TRUNC(-2.9); -- -2
SELECT TRUNC(2.567, 2); -- 2.56The TRUNC is not the same as ROUND. The TRUNC(2.9) is 2, and the ROUND(2.9) is 3. The TRUNC is the function to use when the fractional part should be discarded, not rounded.
The ABS function returns the absolute value.
SELECT ABS(-5); -- 5
SELECT ABS(5); -- 5
SELECT ABS(-2.5); -- 2.5The ABS is used for computing differences without regard to direction.
SELECT ABS(actual - expected) AS error FROM measurements;The SIGN function returns the sign of a number: -1, 0, or 1.
SELECT SIGN(-5); -- -1
SELECT SIGN(0); -- 0
SELECT SIGN(5); -- 1c. Power, roots, and logarithms
The POWER function raises a number to a power. The SQRT function returns the square root. The CBRT function returns the cube root. The EXP function returns e raised to a power. The LN function returns the natural logarithm. The LOG function returns the logarithm to a specified base.
SELECT POWER(2, 10); -- 1024
SELECT SQRT(16); -- 4
SELECT CBRT(27); -- 3
SELECT EXP(1); -- 2.718281828...
SELECT LN(2.718281828); -- 1
SELECT LOG(100); -- 2 (base 10 in PostgreSQL)
SELECT LOG(2, 8); -- 3 (base 2 in PostgreSQL)The LOG function is inconsistent across databases. In PostgreSQL, LOG(x) is the base-10 logarithm and LOG(b, x) is the logarithm to base b. In MySQL, LOG(x) is the natural logarithm and LOG(b, x) is the logarithm to base b. The LOG10 and LOG2 functions are explicit.
-- PostgreSQL
SELECT LOG(100); -- 2 (base 10)
SELECT LOG(2, 8); -- 3 (base 2)
SELECT LOG10(100); -- 2
-- MySQL
SELECT LOG(100); -- 4.605... (natural log)
SELECT LOG(10, 100); -- 2 (base 10)
SELECT LOG10(100); -- 2
SELECT LOG2(8); -- 3The MOD function returns the remainder, same as the % operator.
SELECT MOD(7, 3); -- 1
SELECT MOD(-7, 3); -- -1The PI function returns the value of π. The RADIANS and DEGREES functions convert between the two units.
SELECT PI(); -- 3.14159265358979
SELECT RADIANS(180); -- 3.14159265358979
SELECT DEGREES(PI()); -- 180The trigonometric functions—SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2—operate on radians.
SELECT SIN(PI() / 2); -- 1
SELECT COS(0); -- 1
SELECT TAN(PI() / 4); -- 1The ATAN2(y, x) returns the angle whose tangent is y / x, using the signs of both arguments to determine the quadrant. It is used for computing bearings and directions.
SELECT ATAN2(1, 1); -- 0.785398163397448 (π/4)Complete Example Session
-- ============================================
-- PART 1: BASIC ARITHMETIC
-- ============================================
SELECT 7 + 2; -- 9
SELECT 7 - 2; -- 5
SELECT 7 * 2; -- 14
SELECT 7 / 2; -- 3
SELECT 7 % 2; -- 1-- ============================================
-- PART 2: INTEGER DIVISION
-- ============================================
SELECT 7 / 2; -- 3
SELECT 7.0 / 2; -- 3.5
SELECT 7::numeric / 2; -- 3.5
SELECT CAST(7 AS numeric) / 2; -- 3.5-- ============================================
-- PART 3: NULL IN ARITHMETIC
-- ============================================
SELECT 7 + NULL; -- NULL
SELECT COALESCE(NULL, 0) + 7; -- 7
SELECT COALESCE(quantity, 0) * price FROM order_items;-- ============================================
-- PART 4: ROUND
-- ============================================
SELECT ROUND(2.5); -- 3
SELECT ROUND(2.4); -- 2
SELECT ROUND(2.567, 2); -- 2.57
SELECT ROUND(1234.5, -2); -- 1200-- ============================================
-- PART 5: CEIL AND FLOOR
-- ============================================
SELECT CEIL(2.1); -- 3
SELECT FLOOR(2.9); -- 2
SELECT CEIL(-2.1); -- -2
SELECT FLOOR(-2.1); -- -3-- ============================================
-- PART 6: TRUNC
-- ============================================
SELECT TRUNC(2.9); -- 2
SELECT TRUNC(-2.9); -- -2
SELECT TRUNC(2.567, 2); -- 2.56-- ============================================
-- PART 7: ABS AND SIGN
-- ============================================
SELECT ABS(-5); -- 5
SELECT SIGN(-5); -- -1
SELECT SIGN(0); -- 0
SELECT SIGN(5); -- 1-- ============================================
-- PART 8: POWER AND ROOTS
-- ============================================
SELECT POWER(2, 10); -- 1024
SELECT SQRT(16); -- 4
SELECT CBRT(27); -- 3-- ============================================
-- PART 9: LOGARITHMS
-- ============================================
SELECT LOG(100); -- 2 (base 10 in PostgreSQL)
SELECT LOG(2, 8); -- 3 (base 2)
SELECT LN(2.718281828); -- 1-- ============================================
-- PART 10: PERCENTAGE PATTERN
-- ============================================
SELECT
ROUND(100.0 * successful / NULLIF(total, 0), 2) AS success_rate
FROM stats;The ten parts covered basic arithmetic, integer division, NULL in arithmetic, ROUND, CEIL and FLOOR, TRUNC, ABS and SIGN, power and roots, logarithms, and the percentage pattern.
Quick Reference
Arithmetic Operators
| Operator | Operation |
|---|---|
+ | Addition |
- | Subtraction |
* | Multiplication |
/ | Division |
% | Modulo |
Rounding Functions
| Function | Behavior |
|---|---|
ROUND(x) | Round to nearest integer |
ROUND(x, d) | Round to d decimal places |
ROUND(x, -d) | Round to left of decimal |
CEIL(x) | Round up |
FLOOR(x) | Round down |
TRUNC(x) | Truncate toward zero |
TRUNC(x, d) | Truncate to d places |
Math Functions
| Function | Result |
|---|---|
ABS(x) | Absolute value |
SIGN(x) | -1, 0, or 1 |
POWER(x, y) | x to the power y |
SQRT(x) | Square root |
CBRT(x) | Cube root |
EXP(x) | e to the power x |
LN(x) | Natural logarithm |
LOG(x) | Base 10 (PostgreSQL) |
LOG(b, x) | Base b logarithm |
MOD(x, y) | Remainder |
PI() | π |
RADIANS(x) | Degrees to radians |
DEGREES(x) | Radians to degrees |
Type Casting
| Cast | Purpose |
|---|---|
x::numeric | PostgreSQL cast |
CAST(x AS numeric) | Standard cast |
CAST(x AS decimal(10,2)) | Fixed precision |
CAST(x AS float) | Floating point |
CAST(x AS integer) | Truncates |
Best Practices
✅ Do This:
-- Cast to avoid integer division
SELECT 7::numeric / 2; -- ✅
-- Use COALESCE for NULL in arithmetic
SELECT COALESCE(quantity, 0) * price FROM order_items; -- ✅
-- Use NULLIF to avoid division by zero
SELECT successful / NULLIF(total, 0) FROM stats; -- ✅
-- Use ROUND with a precision for financial values
SELECT ROUND(amount, 2) FROM orders; -- ✅
-- Use CEIL for page counts
SELECT CEIL(COUNT(*) / 10.0) AS pages FROM items; -- ✅
-- Use ABS for differences
SELECT ABS(actual - expected) AS error FROM measurements; -- ✅❌ Don’t Do This:
-- Don't divide integers and expect a fraction
SELECT 7 / 2; -- 3, not 3.5 -- ❌
-- Don't divide by zero
SELECT 1 / 0; -- error -- ❌
-- Don't rely on ROUND for the midpoint
SELECT ROUND(2.5); -- database-dependent -- ⚠️
-- Don't use TRUNC when you mean ROUND
SELECT TRUNC(2.9); -- 2, not 3 -- ⚠️
-- Don't ignore NULL in arithmetic
SELECT quantity * price; -- NULL if either is NULL -- ⚠️
-- Don't assume LOG is base 10
SELECT LOG(100); -- base 10 in PostgreSQL, natural in MySQL -- ⚠️
-- Don't compare floats for equality
WHERE amount = 0.1; -- floating point imprecision -- ⚠️Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Integer division | Both operands are integers | Cast to numeric |
| Division by zero | Zero denominator | Use NULLIF |
NULL in result | NULL operand | Use COALESCE |
| Rounding surprise | Midpoint behavior | Check the database |
TRUNC vs ROUND | Different behavior | Use the right one |
LOG base | Database-specific | Use LOG10 or LN |
| Float comparison | Imprecision | Use a tolerance |
Real-World Examples
1. Percentage
SELECT
ROUND(100.0 * successful / NULLIF(total, 0), 2) AS success_rate
FROM stats;2. Average with Rounding
SELECT ROUND(AVG(amount), 2) AS avg_amount FROM orders;3. Page Count
SELECT CEIL(COUNT(*) / 10.0) AS pages FROM items;4. Absolute Difference
SELECT ABS(actual - expected) AS error FROM measurements;5. Price with Tax
SELECT ROUND(price * 1.2, 2) AS price_with_tax FROM products;6. Discount
SELECT ROUND(price * (1 - discount), 2) AS final_price FROM products;7. Bin into Ranges
SELECT FLOOR(score / 10) * 10 AS bucket, COUNT(*)
FROM scores
GROUP BY 1
ORDER BY 1;8. Modulo for Cycling
SELECT id, id % 3 AS bucket FROM items;9. Power
SELECT POWER(2, 10) AS bytes_in_kb;10. Square Root
SELECT SQRT(area) AS side FROM squares;Visual
Integer vs Numeric Division
┌─────────────────────────────────────────────────────────────┐
│ INTEGER DIVISION │
│ │
│ SELECT 7 / 2; │
│ → 3 (the fractional part is truncated) │
│ │
│ SELECT -7 / 2; │
│ → -3 (truncated toward zero) │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ NUMERIC DIVISION │
│ │
│ SELECT 7.0 / 2; │
│ → 3.5 │
│ │
│ SELECT 7::numeric / 2; │
│ → 3.5 │
│ │
│ SELECT CAST(7 AS numeric) / 2; │
│ → 3.5 │
│ │
└─────────────────────────────────────────────────────────────┘Rounding Functions
┌─────────────────────────────────────────────────────────────┐
│ VALUE ROUND CEIL FLOOR TRUNC │
│ ───────────────────────────────────────── │
│ 2.1 2 3 2 2 │
│ 2.5 3 3 2 2 │
│ 2.9 3 3 2 2 │
│ -2.1 -2 -2 -3 -2 │
│ -2.5 -3 -2 -3 -2 │
│ -2.9 -3 -2 -3 -2 │
│ │
│ ROUND: nearest, half away from zero (numeric) │
│ CEIL: toward positive infinity │
│ FLOOR: toward negative infinity │
│ TRUNC: toward zero │
│ │
└─────────────────────────────────────────────────────────────┘NULL Propagation
┌─────────────────────────────────────────────────────────────┐
│ 7 + NULL → NULL │
│ NULL * 2 → NULL │
│ NULL / 0 → NULL (not an error) │
│ │
│ COALESCE fixes it: │
│ COALESCE(NULL, 0) + 7 → 7 │
│ │
│ Aggregates ignore NULL: │
│ SUM([1, 2, NULL, 3]) → 6 │
│ AVG([1, 2, NULL, 3]) → 2 │
│ COUNT(column) → 3 (non-NULL) │
│ COUNT(*) → 4 (all rows) │
│ │
└─────────────────────────────────────────────────────────────┘Safe Division
┌─────────────────────────────────────────────────────────────┐
│ DIVISION BY ZERO │
│ │
│ SELECT 10 / 0; │
│ → ERROR: division by zero │
│ │
├─────────────────────────────────────────────────────────────┤
│ │
│ SAFE DIVISION WITH NULLIF │
│ │
│ SELECT 10 / NULLIF(0, 0); │
│ → NULLIF(0, 0) = NULL │
│ → 10 / NULL = NULL │
│ → Result: NULL (no error) │
│ │
│ SELECT 10 / NULLIF(5, 0); │
│ → NULLIF(5, 0) = 5 │
│ → 10 / 5 = 2 │
│ → Result: 2 │
│ │
└─────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Operators | +, -, *, /, % |
| Integer division | Truncates toward zero |
| Fix for integer division | Cast to numeric |
ROUND(x, d) | Round to d decimal places |
CEIL(x) | Round up |
FLOOR(x) | Round down |
TRUNC(x) | Truncate toward zero |
ABS(x) | Absolute value |
SIGN(x) | -1, 0, or 1 |
POWER(x, y) | x to the power y |
SQRT(x) | Square root |
CBRT(x) | Cube root |
LN(x) | Natural log |
LOG(b, x) | Base b log |
MOD(x, y) | Remainder |
NULL in arithmetic | Propagates |
Key takeaways:
- Integer division truncates.
7 / 2is3, not3.5. Cast one operand tonumericorfloatto get the fractional part. This is the most common surprise in SQL arithmetic. NULLpropagates through arithmetic.7 + NULLisNULL. The aggregate functions ignoreNULL, but the operators do not. UseCOALESCEto substitute a value.ROUNDrounds half away from zero for numeric types. The exact behavior at the midpoint depends on the database and the type. Check the behavior if the midpoint matters.CEILrounds up,FLOORrounds down,TRUNCtruncates toward zero. The three are different.CEIL(2.1)is3,FLOOR(2.1)is2, andTRUNC(2.9)is2.NULLIFprevents division by zero.10 / NULLIF(0, 0)returnsNULLinstead of an error. The pattern is the standard safe-division idiom.- The
LOGfunction is database-specific. In PostgreSQL,LOG(x)is base 10. In MySQL,LOG(x)is the natural logarithm. UseLOG10,LOG2, orLNfor explicit behavior. - Floating-point comparison is imprecise.
0.1 + 0.2is not exactly0.3in floating-point arithmetic. Use a tolerance for comparisons, or use thenumerictype for exact decimal arithmetic.
Remember: Numeric functions are the tools for arithmetic that goes beyond the operators. Integer division is the trap; cast to avoid it. NULL propagation is the trap; use COALESCE. Division by zero is the trap; use NULLIF. Rounding is the trap; know the behavior. The functions—ROUND, CEIL, FLOOR, TRUNC, ABS, POWER, SQRT, LOG—are the extensions of the operators. Use them, and the arithmetic in the query will produce the values the application expects.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!