SQL Numeric Functions – ROUND, ABS, CEIL, FLOOR

ROUND()

ROUND(number, decimal_places) rounds a number to the specified number of decimal places. When decimal_places is omitted or 0, it rounds to the nearest integer.

SQL

SELECT ROUND(3.14159, 2);     -- 3.14
SELECT ROUND(3.14159, 4);     -- 3.1416
SELECT ROUND(3.7);            -- 4
SELECT ROUND(3.2);            -- 3

-- Negative decimal places rounds to tens, hundreds, etc.
SELECT ROUND(1567, -2);       -- 1600

-- Business example: round prices to 2 decimal places
SELECT
  product_name,
  price,
  ROUND(price * 1.1, 2) AS price_with_tax
FROM products;

ABS()

ABS(number) returns the absolute (non-negative) value of a number. Useful when you care about the magnitude of a difference, not its direction.

SQL

SELECT ABS(-42);     -- 42
SELECT ABS(42);      -- 42
SELECT ABS(-3.14);   -- 3.14

-- Find transactions where actual amount differs from expected by more than $100
SELECT transaction_id, actual_amount, expected_amount,
       ABS(actual_amount - expected_amount) AS discrepancy
FROM transactions
WHERE ABS(actual_amount - expected_amount) > 100
ORDER BY discrepancy DESC;

CEIL() and FLOOR()

CEIL(n) (also CEILING(n)) rounds UP to the nearest integer. FLOOR(n) rounds DOWN to the nearest integer.

SQL

SELECT CEIL(4.1);      -- 5
SELECT CEIL(4.9);      -- 5
SELECT CEIL(-4.1);     -- -4

SELECT FLOOR(4.9);     -- 4
SELECT FLOOR(4.1);     -- 4
SELECT FLOOR(-4.1);    -- -5

-- Practical: calculate number of pages needed (ceiling division)
-- e.g. 25 rows, 10 per page = 3 pages
SELECT CEIL(COUNT(*) / 10.0) AS total_pages
FROM orders;

-- Group customers into age brackets
SELECT
  customer_id,
  age,
  FLOOR(age / 10) * 10 AS age_bracket  -- 20, 30, 40, ...
FROM customers;

MOD()

MOD(a, b) returns the remainder when a is divided by b. MySQL also supports the % operator. Use it to identify even/odd numbers, cycle through values, or extract positional data.

SQL

SELECT MOD(10, 3);    -- 1 (10 ÷ 3 = 3 remainder 1)
SELECT MOD(9, 3);     -- 0 (perfectly divisible)

-- MySQL operator style
SELECT 10 % 3;        -- 1

-- Find all even-numbered orders
SELECT order_id
FROM orders
WHERE MOD(order_id, 2) = 0;

-- Assign customers to 5 segments (0-4) for A/B testing
SELECT customer_id,
       MOD(customer_id, 5) AS test_group
FROM customers;

POWER() and SQRT()

POWER(base, exponent) raises a number to a power. POW() is an alias in MySQL. SQRT(n) returns the square root.

SQL

SELECT POWER(2, 10);    -- 1024
SELECT POWER(9, 0.5);   -- 3.0 (same as SQRT)
SELECT SQRT(144);       -- 12
SELECT SQRT(2);         -- 1.4142135623...

-- Calculate Euclidean distance between two points
SELECT
  point_a,
  point_b,
  SQRT(POWER(x2 - x1, 2) + POWER(y2 - y1, 2)) AS distance
FROM geo_points;

-- Compound interest: A = P * (1 + r)^n
SELECT
  principal,
  ROUND(principal * POWER(1 + interest_rate, years), 2) AS future_value
FROM investments;

RANDOM() / RAND()

Generates a random floating-point number between 0 and 1. The function name differs by database: RAND() in MySQL, RANDOM() in PostgreSQL and SQLite.

SQL

-- MySQL
SELECT RAND();                   -- e.g. 0.3847291...

-- PostgreSQL / SQLite
SELECT RANDOM();                 -- e.g. 0.7293485...

-- Sample 10 random rows (MySQL)
SELECT *
FROM customers
ORDER BY RAND()
LIMIT 10;

-- Sample 10 random rows (PostgreSQL)
SELECT *
FROM customers
ORDER BY RANDOM()
LIMIT 10;

-- Random integer between 1 and 100 (MySQL)
SELECT FLOOR(RAND() * 100) + 1 AS random_int;

⚠️

ORDER BY RAND() is slow on large tables

Using ORDER BY RAND() forces a full table scan and sort. On tables with millions of rows, use a more efficient random sampling technique such as WHERE id >= (SELECT FLOOR(MAX(id)*RAND()) FROM t) LIMIT 1 or tablesample (PostgreSQL).

Summary

📋 Summary

ROUND(n, d) — Round to d decimal places. Negative d rounds to tens, hundreds.
ABS(n) — Absolute value. Use for magnitude comparisons.
CEIL(n) — Round up to next integer. Good for pagination counts.
FLOOR(n) — Round down to previous integer. Good for age brackets.
MOD(a, b) — Remainder of division. Use for even/odd checks and segmentation.
POWER(b, e) — Raise to a power. SQRT() for square root.
RAND() / RANDOM() — Random float 0–1. Use with FLOOR() for random integers.
Direct arithmetic — salary * 0.85, price / 1.2, units - returns all work inline in SELECT.

FAQ

What is the difference between ROUND, CEIL, and FLOOR?+

ROUND(3.5) = 4 (rounds to nearest, ties go up); CEIL(3.1) = 4 (always rounds up); FLOOR(3.9) = 3 (always rounds down). For negative numbers: CEIL(-3.1) = -3 (closer to zero), FLOOR(-3.1) = -4 (further from zero).

How do I perform integer division in SQL?+

In MySQL, the DIV operator performs integer division: 7 DIV 2 = 3. Alternatively, use FLOOR(7 / 2) = 3. In PostgreSQL, dividing two integers performs integer division automatically: 7 / 2 = 3. To force decimal division, cast one operand: 7 / 2.0 = 3.5 or 7::numeric / 2.

Does RAND() return a different value every time it is called in a query?+

Yes — each call to RAND() or RANDOM() returns a new random number. This means every row in a result set gets a different random value when you use ORDER BY RAND(). To get a reproducible random sequence, seed the function: RAND(42) in MySQL or SETSEED(0.5) before RANDOM() in PostgreSQL.

Can I use arithmetic expressions in WHERE clauses?+

Yes. Arithmetic works in WHERE, SELECT, ORDER BY, and HAVING. For example: WHERE salary * 1.1 > 50000 or WHERE price / quantity < 5.00. Note that using expressions on indexed columns in WHERE can prevent index usage — where possible, rearrange the expression to isolate the column: WHERE salary > 50000 / 1.1.