Numeric & Math Functions

Rounding

SELECT ROUND(3.567, 2);   -- 3.57
SELECT ROUND(3.567);      -- 4
SELECT ROUND(1234, -2);   -- 1200
SELECT CEIL(3.2);         -- 4   (also CEILING)
SELECT FLOOR(3.9);        -- 3
SELECT TRUNCATE(3.567,1); -- 3.5 (cuts, does not round)

Arithmetic helpers

SELECT ABS(-15), SIGN(-7), MOD(10, 3), 10 % 3;
SELECT POW(2, 10), SQRT(144), EXP(1), LOG(100), LOG10(1000);
SELECT GREATEST(3, 9, 2), LEAST(3, 9, 2);
SELECT PI(), RAND(), FLOOR(RAND() * 100) + 1;   -- random 1-100

Integer division and NULL-safe division

SELECT 7 DIV 2;      -- 3  (integer division)
SELECT 7 / 2;        -- 3.5000
SELECT 7 / 0;        -- NULL, not an error

MySQL returns NULL for division by zero instead of failing — guard against it:

SELECT total / NULLIF(count_col, 0) AS safe_avg FROM stats;

Formatting numbers

SELECT FORMAT(1234567.891, 2);       -- '1,234,567.89' (a string)
SELECT CAST(3.7 AS SIGNED);          -- 4
SELECT CONVERT('123', UNSIGNED);     -- 123
SELECT CAST('2024-01-15' AS DATE);

Practical examples

-- percentage with one decimal
SELECT name_en, ROUND(cgpa / 4.0 * 100, 1) AS percent FROM students;

-- price after 15% VAT, rounded to 2 decimals
SELECT item, ROUND(price * 1.15, 2) AS price_with_vat FROM products;

-- bucket ages into 10-year groups
SELECT FLOOR(age / 10) * 10 AS age_group, COUNT(*)
FROM students GROUP BY age_group ORDER BY age_group;

-- even / odd
SELECT id, IF(MOD(id, 2) = 0, 'even', 'odd') AS parity FROM students;

Remember the DECIMAL rule from the data-types page: do arithmetic on DECIMAL for money. ROUND() on a DOUBLE can still surprise you because the underlying value was never exact.