SQL Numeric Functions

Concept

Numeric functions do arithmetic the operators cannot express. The important ones are about rounding, because that is where money quietly goes missing.

Syntax

Example

SELECT first_name,
       salary,
       ROUND(salary / 12, 2)        AS monthly,
       ROUND(salary * 0.18, 0)      AS tax_estimate,
       FLOOR(salary / 10000)        AS salary_band,
       MOD(id, 2)                   AS id_parity
FROM   employees
ORDER BY salary DESC;

Explanation

FLOOR(salary / 10000) is a neat way to bucket a continuous value into bands without a long CASE. MOD(id, 2) splits rows into two even groups - useful for sampling and for A/B style reporting.

Integer division

-- MySQL and MariaDB return a decimal
SELECT 7 / 2;              -- 3.5000
SELECT 7 DIV 2;            -- 3, explicit integer division

-- PostgreSQL, SQL Server and Oracle truncate when both sides are integers
-- SELECT 7 / 2;           -- 3
-- SELECT 7 * 1.0 / 2;     -- 3.5

Rounding is not one rule

SELECT ROUND(2.5), ROUND(3.5), ROUND(-2.5);

Most engines round halves away from zero here (3, 4, -3), but some numeric types and some products use banker's rounding (round half to even), giving 2, 4, -2. If a total must reconcile to the penny, never assume - test on the exact engine, and do the final rounding in one agreed place.

Important rules

• Rounding a FLOAT can still surprise you, because the stored value was never exactly what you typed. Round DECIMAL values.
• Division by zero raises an error in PostgreSQL, SQL Server and Oracle. MySQL and MariaDB return NULL by default, and error only in strict mode.
• NULL in, NULL out - for every one of these functions.
• ROUND(n, d) with a negative d rounds to tens, hundreds and so on: ROUND(1234, -2) is 1200.

Common mistakes

• Rounding at every intermediate step and accumulating drift. Round once, at the end.
• Using FLOOR where ROUND was meant, and losing a rupee per row.
• Assuming FLOOR(-1.2) is -1. It is -2; FLOOR moves toward negative infinity, TRUNCATE moves toward zero.
• Dividing by a column that can be zero without guarding it with NULLIF(divisor, 0).

Best practices

• Use DECIMAL for money and round exactly once, as late as possible.
• Guard every division: total / NULLIF(quantity, 0) returns NULL instead of erroring.
• Agree one rounding rule across the application, the database and the reporting tool.
• Keep raw values stored; compute derived values in the query or a view.

Practice

1. Return each order's total and the total rounded to the nearest hundred.
2. Compute the average unit price per order item, guarding against a zero quantity.
3. What do FLOOR(-2.5), CEILING(-2.5) and TRUNCATE(-2.5, 0) each return, and why are they all different?