Numeric functions

Arithmetic

MySQL can calculate with columns and constants. The basic operators are +, -, *, /, % (remainder, also written MOD) and DIV (integer division):

SELECT 7 + 2 AS plus, 7 - 2 AS minus, 7 * 2 AS times, 7 / 2 AS divide, 7 DIV 2 AS whole, 7 % 2 AS remainder;

+------+-------+-------+--------+-------+-----------+
| plus | minus | times | divide | whole | remainder |
+------+-------+-------+--------+-------+-----------+
|    9 |     5 |    14 | 3.5000 |     3 |         1 |
+------+-------+-------+--------+-------+-----------+

Dividing by zero does not crash a query. It returns NULL (with a warning, and an error in strict mode inside INSERT or UPDATE):

SELECT 5 / 0 AS result;

+--------+
| result |
+--------+
|   NULL |
+--------+

Rounding

Four functions cover almost all rounding needs:

SELECT ROUND(2.567) AS r0, ROUND(2.567, 2) AS r2, CEIL(2.1) AS ceil_up, FLOOR(2.9) AS floor_down, TRUNCATE(2.567, 1) AS cut;

+----+------+---------+------------+-----+
| r0 | r2   | ceil_up | floor_down | cut |
+----+------+---------+------------+-----+
|  3 | 2.57 |       3 |          2 | 2.5 |
+----+------+---------+------------+-----+

Negative digits round to the left of the decimal point: ROUND(1234, -2) returns 1200.

Use them on real columns, for example to show prices with tax rounded to two places:

SELECT name, price, ROUND(price * 1.18, 2) AS price_with_gst FROM products WHERE category_id = 2;

+-----------------------------+-------+----------------+
| name                        | price | price_with_gst |
+-----------------------------+-------+----------------+
| SQL Basics Handbook         | 29.95 |          35.34 |
| Web Design Patterns         | 34.50 |          40.71 |
| Data Structures Illustrated | 45.00 |          53.10 |
+-----------------------------+-------+----------------+

Other everyday functions

SELECT ABS(-15) AS absolute, POW(2, 10) AS power, SQRT(81) AS root, SIGN(-4) AS sign, MOD(17, 5) AS remainder;

+----------+-------+------+------+-----------+
| absolute | power | root | sign | remainder |
+----------+-------+------+------+-----------+
|       15 |  1024 |    9 |   -1 |         2 |
+----------+-------+------+------+-----------+

GREATEST and LEAST pick the largest or smallest of several values in one row, which is different from MAX and MIN, which scan many rows:

SELECT GREATEST(3, 9, 5) AS biggest, LEAST(3, 9, 5) AS smallest;

+---------+----------+
| biggest | smallest |
+---------+----------+
|       9 |        3 |
+---------+----------+

A useful pattern is clamping a value so it never goes below zero: GREATEST(stock - 10, 0).

Random numbers

RAND() returns a number between 0 and 1 that changes every call. It is useful for random samples:

SELECT name FROM products ORDER BY RAND() LIMIT 3;

That picks three random products. Note that ORDER BY RAND() sorts the whole table, so avoid it on large tables.

Exact versus approximate numbers

This is a topic that causes real bugs. MySQL has two families of non-integer types:

• DECIMAL(p, s) stores exact decimal numbers. p is the total digits and s the digits after the point.
• FLOAT and DOUBLE store approximate binary fractions.

Many decimal fractions, like 0.1, cannot be represented exactly in binary. Compare:

SELECT 0.1 + 0.2 AS decimal_math, 0.1e0 + 0.2e0 AS float_math, (0.1e0 + 0.2e0) = 0.3 AS float_equals;

+--------------+---------------------+--------------+
| decimal_math | float_math          | float_equals |
+--------------+---------------------+--------------+
|          0.3 | 0.30000000000000004 |            0 |
+--------------+---------------------+--------------+

The first value is exact. The second, written in scientific notation so that it is a DOUBLE, shows the tiny error, and comparing it for equality gives false. That is why you store prices in DECIMAL, as the sample products.price column does, and never in FLOAT. The data types lesson comes back to this.

Choosing rounding that matches the business rule

Rounding is not only a technical detail. Invoices, tax and discounts usually follow a rule written by an accountant or by law, such as "round each line to two decimals, then add" or "add everything, then round once". The two approaches can differ by a few paise. Decide the rule first, apply it in one place (a view or a function), and use the same one in every report. ROUND on a DECIMAL rounds halves away from zero, while on a DOUBLE the result depends on the binary value, another reason to keep money in DECIMAL.

Integer overflow

Integer columns have limits. A TINYINT holds -128 to 127 and an INT about plus or minus 2.1 billion. Arithmetic that exceeds the range raises an "out of range" error. Choose the type that fits the data. For ids that may grow large, use BIGINT.

Common mistakes

• Expecting / to return a whole number.
• Storing money in FLOAT.
• Rounding too early. Round at the end of a calculation, not in the middle, or the errors add up.
• Comparing floating point numbers with =.
• Assuming RAND() is cheap on big tables.

Exercise

For each product in category 4 (Sports), show its name, the price rounded up to the next whole currency unit, and the number of whole boxes of 12 that the current stock could fill (use integer division).

SELECT name, CEIL(price) AS price_rounded_up, stock DIV 12 AS full_boxes
FROM products
WHERE category_id = 4;

+------------------+------------------+------------+
| name             | price_rounded_up | full_boxes |
+------------------+------------------+------------+
| Yoga Mat         |               28 |          9 |
| Resistance Bands |               15 |         25 |
| Water Bottle     |               12 |         20 |
+------------------+------------------+------------+