After this lesson you can do the arithmetic that reports need: monthly figures from yearly ones, percentage raises, differences between two columns, and neatly rounded results. You will also know about the one trap in SQL arithmetic that catches almost every beginner, whole number division.
The examples run on the sample company database, tested on SQLite. Where a result would look different on another database, the text says so.
Arithmetic basics
SQL has the four familiar operators, +, -, * and /, plus % for the remainder after a division. You can use them on columns, on fixed numbers or on other calculations, and you can use brackets to control the order. Multiplication and division happen before addition and subtraction, as in school maths.
SELECT first_name,
salary,
salary * 12 AS twelve_times,
salary + 5000 AS plus_bonus
FROM employees
WHERE id <= 3;+------------+--------+--------------+------------+
| first_name | salary | twelve_times | plus_bonus |
+------------+--------+--------------+------------+
| Meera | 180000 | 2160000 | 185000 |
| Arjun | 125000 | 1500000 | 130000 |
| Kavya | 125000 | 1500000 | 130000 |
+------------+--------+--------------+------------+Remember that these calculations only appear in the result. The table keeps the original salary. Giving the calculated columns names with AS is covered in the lesson on aliases.
The whole number division trap
Look at what happens when you ask for a monthly salary:
SELECT first_name,
salary,
salary / 12 AS monthly_whole,
salary / 12.0 AS monthly_decimal
FROM employees
WHERE id <= 5;+------------+--------+---------------+--------------------+
| first_name | salary | monthly_whole | monthly_decimal |
+------------+--------+---------------+--------------------+
| Meera | 180000 | 15000 | 15000.0 |
| Arjun | 125000 | 10416 | 10416.666666666666 |
| Kavya | 125000 | 10416 | 10416.666666666666 |
| Rohan | 82000 | 6833 | 6833.333333333333 |
| Isha | 78000 | 6500 | 6500.0 |
+------------+--------+---------------+--------------------+Arjun's salary of 125000 divided by 12 is 10416.67, but the third column shows 10416, while the fourth column shows the fraction (its long tail of decimals is how floating point numbers print, which the section on exact decimals explains). When both numbers are whole numbers, SQLite, PostgreSQL and SQL Server divide as whole numbers and discard the fraction. They do not round, they cut off. Writing 12.0 instead of 12 makes one side a decimal, so the division keeps its fraction.
MySQL behaves differently: / always gives a decimal result there, and a separate DIV operator does whole number division. This is a classic source of bugs when a query moves from one database to another, so if you divide two integer columns, make your intent explicit with a decimal literal or a cast (see the lesson on CAST).
Rounding
ROUND(value, places) rounds to the given number of decimal places. Leave out the second argument to round to a whole number. To round to the nearest ten or hundred, divide, round and multiply back. This works everywhere:
SELECT ROUND(2.567, 2) AS two_places,
ROUND(2.567) AS whole,
ROUND(1234.5678 / 100.0) * 100 AS nearest_hundred;+------------+-------+-----------------+
| two_places | whole | nearest_hundred |
+------------+-------+-----------------+
| 2.57 | 3.0 | 1200.0 |
+------------+-------+-----------------+MySQL, PostgreSQL and SQL Server also let you pass a negative number of places, as in ROUND(1234.5678, -2), to round to the left of the decimal point. SQLite treats a negative number of places as zero and rounds to a whole number instead, so the divide-and-multiply way is the portable choice.
SQLite shows rounded results with a trailing .0 because it returns them as decimal numbers. Other databases show the same value in their own formatting, for example 3 or 3.00. The value is the same, only the display differs.
Rounding for display is fine. Rounding in the middle of a calculation is risky, because the small errors add up. Calculate with full precision first, then round once at the end:
SELECT first_name,
salary,
ROUND(salary / 12.0, 2) AS monthly,
ROUND(salary * 1.075) AS after_raise
FROM employees
WHERE id IN (2, 4, 5);+------------+--------+----------+-------------+
| first_name | salary | monthly | after_raise |
+------------+--------+----------+-------------+
| Arjun | 125000 | 10416.67 | 134375.0 |
| Rohan | 82000 | 6833.33 | 88150.0 |
| Isha | 78000 | 6500.0 | 83850.0 |
+------------+--------+----------+-------------+How exact halves such as 2.5 are rounded can depend on the database and on the data type, so if it matters, test your own system.
ABS, CEILING and FLOOR
ABS removes the sign of a number, which is handy for distances. CEILING rounds up to the next whole number and FLOOR rounds down. For negative numbers, remember that "down" means further from zero, so FLOOR(-2.1) is -3.
SELECT ABS(-7) AS absolute,
CEILING(2.1) AS rounded_up,
FLOOR(2.9) AS rounded_down,
FLOOR(-2.1) AS floor_negative;+----------+------------+--------------+----------------+
| absolute | rounded_up | rounded_down | floor_negative |
+----------+------------+--------------+----------------+
| 7 | 3.0 | 2.0 | -3.0 |
+----------+------------+--------------+----------------+Here is a real use of ABS. The query below finds who is closest to a salary target of 100000, by sorting on the distance in either direction:
SELECT first_name,
salary,
ABS(salary - 100000) AS distance
FROM employees
WHERE id <= 4
ORDER BY distance;+------------+--------+----------+
| first_name | salary | distance |
+------------+--------+----------+
| Rohan | 82000 | 18000 |
| Arjun | 125000 | 25000 |
| Kavya | 125000 | 25000 |
| Meera | 180000 | 80000 |
+------------+--------+----------+Powers, roots and remainders
POWER(base, exponent) and SQRT(x) do what their names say. The remainder operator % is useful for repeating patterns. For example, id % 2 = 0 picks out the even ids, and salary % 1000 tells you whether a salary is a round thousand.
SELECT POWER(2, 10) AS two_to_ten,
SQRT(144) AS root,
17 % 5 AS remainder;+------------+------+-----------+
| two_to_ten | root | remainder |
+------------+------+-----------+
| 1024.0 | 12.0 | 2 |
+------------+------+-----------+SELECT id, first_name
FROM employees
WHERE id % 2 = 0 AND id <= 10;+----+------------+
| id | first_name |
+----+------------+
| 2 | Arjun |
| 4 | Rohan |
| 6 | Vikram |
| 8 | Sameer |
| 10 | Dev |
+----+------------+Floating point and exact decimals
Computers store many decimal fractions approximately. This is a property of floating point numbers and is the same in every language:
SELECT 0.1 + 0.2 AS raw_sum,
ROUND(0.1 + 0.2, 2) AS tidy_sum;+---------------------+----------+
| raw_sum | tidy_sum |
+---------------------+----------+
| 0.30000000000000004 | 0.3 |
+---------------------+----------+For money, databases offer exact types such as DECIMAL and NUMERIC, which you will meet in the lesson on data types. For display, ROUND is enough.
Dividing by zero
What happens when you divide by zero depends on the database. PostgreSQL and SQL Server stop with an error. SQLite returns NULL:
SELECT 7 / 0 AS result;+--------+
| result |
+--------+
| NULL |
+--------+A query that works on one database and crashes on another is a nasty surprise. The usual safe pattern is NULLIF, which turns a zero into NULL before you divide: x / NULLIF(y, 0). You will see it in a later lesson.
Differences between databases
- Whole number division. As described above,
10 / 4is 2 on SQLite, PostgreSQL and SQL Server but 2.5 on MySQL. - Ceiling.
CEILINGworks in MySQL, PostgreSQL, SQL Server and SQLite.CEILis a common short form available in MySQL, PostgreSQL and SQLite, but SQL Server only hasCEILING. - Remainder.
%works in all four. TheMOD(a, b)function exists in MySQL, PostgreSQL and SQLite, but not in SQL Server. - Several-value maximum. Picking the biggest of several values in one row is
GREATEST(a, b, c)in MySQL and PostgreSQL. SQLite uses a multi-argumentMAX(a, b, c)instead, which is easy to confuse with the aggregate you will meet in the next section. - Math functions in SQLite. Functions such as
POWER,SQRT,CEILINGandFLOORare part of an optional SQLite build setting. The common builds include them, but a minimal build might not.
For MySQL specifics, see numeric functions in MySQL.
Common mistakes
- Dividing two integers and expecting decimals. Use
12.0or a cast to force decimal division. - Rounding too early. Round the final result, not the pieces.
- Dividing without checking for zero. Guard with
NULLIF. - Treating
ROUNDas a data change. It only affects the query result, not the stored value. - Forgetting NULL.
salary + NULLisNULL. A single missing value makes the whole calculation unknown.
Exercise
For every employee in department 2 (Sales), show the first name, salary, a column monthly with the salary divided by 12 and rounded to 2 decimal places, and a column gap_to_100k with the absolute difference between the salary and 100000. Sort by gap_to_100k with the smallest first.
Show one possible answer
SELECT first_name,
salary,
ROUND(salary / 12.0, 2) AS monthly,
ABS(salary - 100000) AS gap_to_100k
FROM employees
WHERE department_id = 2
ORDER BY gap_to_100k;+------------+--------+---------+-------------+
| first_name | salary | monthly | gap_to_100k |
+------------+--------+---------+-------------+
| Neha | 90000 | 7500.0 | 10000 |
| Sameer | 90000 | 7500.0 | 10000 |
| Tara | 52000 | 4333.33 | 48000 |
| Vikram | 150000 | 12500.0 | 50000 |
| Aman | 50000 | 4166.67 | 50000 |
+------------+--------+---------+-------------+Key points
- SQL supports + - * / and the remainder operator %, and you can use them on columns, literals and other expressions.
- Dividing one whole number by another can silently throw away the fraction. Make one side a decimal, for example 12.0, to keep it.
- ROUND(x, n) keeps n decimal places. To round to the nearest hundred on every database, divide, round and multiply back. ABS, CEILING, FLOOR, POWER and SQRT cover the usual needs.
- Rounding is for presenting a result. Do your calculation first and round at the end.