Numbers and maths

Why maths inside the database?

Reports need numbers that are not stored: a price with GST, a discount, a percentage, a daily rate from a monthly salary. Working them out in the query means every screen and report uses the same formula, and only the result travels to the app. In module 2 you met + − * / and ROUND; this topic goes deeper into the traps.

Integer division, again and properly

An integer is a whole number, like 7 or 95000. A real (or decimal) number has a fraction, like 3.5. When both sides of / are integers, SQLite, PostgreSQL and SQL Server give an integer answer and simply drop the fraction. MySQL and Oracle give a decimal answer.

SELECT name, salary, salary / 30 AS wrong_day, ROUND(salary / 30.0, 2) AS day_pay
FROM employees WHERE id IN (3, 9);
-- Anita Desai | 95000 | 3166 | 3166.67
-- Neha Joshi  | 68000 | 2266 | 2266.67

CAST: changing the type

CAST(value AS type) converts a value. CAST(7 AS REAL) gives 7.0, CAST('42' AS INTEGER) turns text into the number 42, and CAST(3.99 AS INTEGER) gives 3, because CAST cuts off the fraction instead of rounding. Use ROUND when you want rounding.

ROUND and ABS

ROUND(x, 2) keeps two decimal places; ROUND(x) keeps none. SQLite rounds halves away from zero, so ROUND(2.5) is 3 and ROUND(-2.5) is −3. ABS(x) gives the size of a number without its sign: ABS(−250) is 250, handy for the difference between two amounts when you do not care which is bigger.

SELECT 1.1 + 2.2;            -- 3.3000000000000003
SELECT ROUND(1.1 + 2.2, 2);  -- 3.3

% : the remainder

a % b gives what is left over after dividing a by b. 17 % 5 is 2. Two common uses: id % 2 = 0 picks even ids (useful to split rows into two groups), and stock % 6 gives the loose items after packing boxes of six. PostgreSQL, MySQL and Oracle also have MOD(a, b).

SELECT name, stock, stock / 6 AS full_boxes, stock % 6 AS loose
FROM products WHERE id <= 3;
-- Basmati Rice 5kg | 40 | 6  | 4
-- Toor Dal 1kg     | 75 | 12 | 3
-- Steel Tiffin Box | 12 | 2  | 0

GST and discount maths

• GST amount: price * 0.18. Price with GST: price * 1.18.
• A discount of d percent: price * (1 − d / 100.0). A 15% discount is price * 0.85.
• Discount first, then GST: price * 0.9 * 1.18. Multiplication order does not change the answer, but say clearly in the report which came first.
• Percentage of a total: part * 100.0 / total, then ROUND.

SELECT name, price, ROUND(price * 0.18, 2) AS gst, ROUND(price * 1.18, 2) AS total
FROM products WHERE id IN (2, 5);
-- Toor Dal 1kg | 160 | 28.8   | 188.8
-- Cotton Kurta | 899 | 161.82 | 1060.82

Common mistakes

• Integer division giving 0 or a cut-off answer. Put the 100.0 or 1.0 first: x * 100.0 / y.
• Expecting CAST(3.99 AS INTEGER) to give 4. CAST cuts; ROUND rounds.
• Rounding in the middle of a calculation and then adding: small differences pile up. Round the final value.
• Comparing reals for exact equality: 1.1 + 2.2 = 3.3 is false in SQLite. Compare rounded values.
• Dividing by a column that can be 0. Topic 5 shows NULLIF for that.