Rounding
query.sql
SQL
SELECT ROUND(2.567) AS to_integer,
ROUND(2.567, 2) AS two_places,
ROUND(1234.567, -2) AS to_hundreds,
TRUNCATE(2.567, 2) AS cut_not_rounded,
CEIL(2.1) AS ceiling,
FLOOR(2.9) AS floor_value,
CEIL(-2.1) AS ceiling_negative,
FLOOR(-2.1) AS floor_negative;
Output
OUTPUT
+------------+------------+-------------+-----------------+---------+-------------+------------------+----------------+
| to_integer | two_places | to_hundreds | cut_not_rounded | ceiling | floor_value | ceiling_negative | floor_negative |
+------------+------------+-------------+-----------------+---------+-------------+------------------+----------------+
| 3 | 2.57 | 1200 | 2.56 | 3 | 2 | -2 | -3 |
+------------+------------+-------------+-----------------+---------+-------------+------------------+----------------+
1 row in set| Function | Meaning |
|---|---|
ROUND(x, d) | Round to d decimal places (a negative d rounds the digits left of the point) |
TRUNCATE(x, d) | Cut to d places without rounding |
CEIL(x) / CEILING(x) | Smallest integer not less than x |
FLOOR(x) | Largest integer not greater than x |
Note how CEIL(-2.1) is -2 and FLOOR(-2.1) is -3.
How halves are rounded
MySQL rounds exact numbers (DECIMAL) half away from zero, but approximate numbers (FLOAT, DOUBLE) by the C library’s rule, which is “half to even”:
query.sql
SQL
SELECT ROUND(2.5) AS decimal_2_5,
ROUND(3.5) AS decimal_3_5,
ROUND(-2.5) AS decimal_minus_2_5,
ROUND(2.5e0) AS double_2_5,
ROUND(3.5e0) AS double_3_5;
Output
OUTPUT
+-------------+-------------+-------------------+------------+------------+
| decimal_2_5 | decimal_3_5 | decimal_minus_2_5 | double_2_5 | double_3_5 |
+-------------+-------------+-------------------+------------+------------+
| 3 | 4 | -3 | 2 | 4 |
+-------------+-------------+-------------------+------------+------------+
1 row in setWith exact numbers, 2.5 becomes 3 (away from zero). With the floating-point 2.5e0, it becomes 2 (to the even neighbour). If rounding of money matters, store and calculate with DECIMAL (DECIMAL vs FLOAT).
Absolute value, sign, remainder
query.sql
SQL
SELECT ABS(-15) AS absolute,
SIGN(-15) AS sign_negative,
SIGN(0) AS sign_zero,
MOD(17, 5) AS remainder,
17 % 5 AS remainder_operator,
17 DIV 5 AS whole_quotient,
MOD(-17, 5) AS negative_remainder;
Output
OUTPUT
+----------+---------------+-----------+-----------+--------------------+----------------+--------------------+
| absolute | sign_negative | sign_zero | remainder | remainder_operator | whole_quotient | negative_remainder |
+----------+---------------+-----------+-----------+--------------------+----------------+--------------------+
| 15 | -1 | 0 | 2 | 2 | 3 | -2 |
+----------+---------------+-----------+-----------+--------------------+----------------+--------------------+
1 row in setMOD(a, b) keeps the sign of the first number. It is the way to test “even or odd” (MOD(n, 2) = 0) and to spread rows into buckets (MOD(id, 4)).
Powers and roots
query.sql
SQL
SELECT POWER(2, 10) AS two_to_the_ten, SQRT(144) AS square_root, EXP(0) AS e_to_zero, LN(1) AS natural_log, LOG10(1000) AS log_base_10, PI() AS pi;
Output
OUTPUT
+----------------+-------------+-----------+-------------+-------------+----------+
| two_to_the_ten | square_root | e_to_zero | natural_log | log_base_10 | pi |
+----------------+-------------+-----------+-------------+-------------+----------+
| 1024 | 12 | 1 | 0 | 3 | 3.141593 |
+----------------+-------------+-----------+-------------+-------------+----------+
1 row in setSmallest and largest of several values
MIN() and MAX() are aggregate functions that work across rows. LEAST() and GREATEST() compare values in the same row:
query.sql
SQL
SELECT LEAST(7, 3, 9) AS smallest, GREATEST(7, 3, 9) AS largest, LEAST(7, NULL, 9) AS with_null, GREATEST('apple', 'banana') AS text_compare;
Output
OUTPUT
+----------+---------+-----------+--------------+
| smallest | largest | with_null | text_compare |
+----------+---------+-----------+--------------+
| 3 | 9 | NULL | banana |
+----------+---------+-----------+--------------+
1 row in setIf any argument is NULL, the result is NULL.
A practical use is clamping a value into a range:
query.sql
SQL
CREATE TABLE ratings (id INT, raw_score INT);
INSERT INTO ratings VALUES (1, -5), (2, 3), (3, 12);
SELECT id, raw_score, LEAST(10, GREATEST(0, raw_score)) AS clamped_0_to_10 FROM ratings;
Output
OUTPUT
+------+-----------+-----------------+
| id | raw_score | clamped_0_to_10 |
+------+-----------+-----------------+
| 1 | -5 | 0 |
| 2 | 3 | 3 |
| 3 | 12 | 10 |
+------+-----------+-----------------+
3 rows in setDivision by zero and NULLIF
Division by zero gives NULL in a SELECT. NULLIF(a, b) returns NULL when the two are equal, which is a neat guard:
query.sql
SQL
CREATE TABLE stats (views INT, clicks INT);
INSERT INTO stats VALUES (100, 5), (0, 0), (50, 10);
SELECT views, clicks, ROUND(100 * clicks / NULLIF(views, 0), 1) AS click_rate_percent FROM stats;
Output
OUTPUT
+-------+--------+--------------------+
| views | clicks | click_rate_percent |
+-------+--------+--------------------+
| 100 | 5 | 5.0 |
| 0 | 0 | NULL |
| 50 | 10 | 20.0 |
+-------+--------+--------------------+
3 rows in setRandom numbers
RAND() returns a random decimal between 0 (inclusive) and 1 (exclusive). A random integer from 1 to 6:
query.sql
SQL
SELECT 1 + FLOOR(RAND() * 6) BETWEEN 1 AND 6 AS dice_in_range;
Output
OUTPUT
+---------------+
| dice_in_range |
+---------------+
| 1 |
+---------------+
1 row in setRAND() is not suitable for security. Generate tokens in your application with a secure generator.
Number formatting
FORMAT(number, decimals) returns a text value with thousand separators. Use it only for display:
query.sql
SQL
SELECT FORMAT(1234567.891, 2) AS formatted, FORMAT(0.5, 0) AS rounded_half;
Output
OUTPUT
+--------------+--------------+
| formatted | rounded_half |
+--------------+--------------+
| 1,234,567.89 | 1 |
+--------------+--------------+
1 row in set
lightbulb
Round at the end
Keep full precision during a calculation and round only the final result. Rounding each step makes errors add up.