Numbers and Arithmetic

SQL supports the usual arithmetic operators — +, -, *, / — and % (remainder) in most databases.

Watch out for integer division

SELECT 7 / 2 AS integer_division, 7.0 / 2 AS decimal_division, 7 % 2 AS remainder;

+------------------+------------------+-----------+
| integer_division | decimal_division | remainder |
+------------------+------------------+-----------+
|                3 |              3.5 |         1 |
+------------------+------------------+-----------+

When both values are integers, SQLite, PostgreSQL, and SQL Server perform integer division and drop the remainder. (MySQL returns 3.5000.) To get a decimal result, make one side a decimal — 7.0 / 2 — or CAST it.

This commonly bites when computing percentages:

SELECT
  COUNT(*) AS orders,
  SUM(status = 'delivered') AS delivered,
  100 * SUM(status = 'delivered') / COUNT(*) AS wrong_pct,
  ROUND(100.0 * SUM(status = 'delivered') / COUNT(*), 1) AS pct
FROM orders;

+--------+-----------+-----------+------+
| orders | delivered | wrong_pct | pct  |
+--------+-----------+-----------+------+
|      8 |         3 |        37 | 37.5 |
+--------+-----------+-----------+------+

(SUM(status = 'delivered') counts matching rows because a true comparison is 1 in SQLite and MySQL. The portable form is SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) — CASE is covered in Level 2.)

Rounding and other functions

SELECT
  ROUND(249.99 * 1.2, 2) AS rounded,
  ROUND(3.5)             AS nearest,
  ABS(-42)               AS absolute,
  MAX(3, 9)              AS larger;

+---------+---------+----------+--------+
| rounded | nearest | absolute | larger |
+---------+---------+----------+--------+
|  299.99 |     4.0 |       42 |      9 |
+---------+---------+----------+--------+

ROUND(value, places) is standard. CEIL/CEILING and FLOOR round up and down. Note that MAX(a, b) with two arguments is SQLite-specific; other databases use GREATEST(a, b), and MAX is otherwise an aggregate function (see the Aggregates lesson).

Practice

Show each product's name and its price with a 15% discount, rounded to two decimal places.

SELECT name, ROUND(price * 0.85, 2) AS sale_price
FROM products;

+-----------------------------+------------+
|            name             | sale_price |
+-----------------------------+------------+
| Mechanical Keyboard         |      75.65 |
| USB-C Hub                   |      30.18 |
| 27-inch Monitor             |     212.49 |
| Laptop Stand                |       35.7 |
| Noise-Cancelling Headphones |     169.15 |
| Webcam                      |      50.91 |
+-----------------------------+------------+