Chapter 09 · basic · 6 min
Arithmetic operators & precedence
SQL isn't just for reading data back; you can compute new values right inside a query. Add, subtract, multiply, and divide columns like you would in a spreadsheet formula. The one thing that trips almost everyone up at least once: dividing two whole numbers truncates, unless you nudge SQL into doing decimal math.
We'll compute discounted order totals for a retail chain.
The dataset
An order_items table: each line has a quantity, a unit price, and a discount percentage stored as a whole number.
Schema
| id | INT |
| product | TEXT |
| quantity | INT |
| unit_price | NUMERIC |
| discount_pct | INT |
Example data
The basic operators
+, -, *, / work exactly like you'd expect, directly on columns. This computes a subtotal for each line before any discount.
Before you run it — Earbuds is 5 units at 89.00 each. What subtotal do you expect for that row?
Precedence and parentheses
Multiplication and division happen before addition and subtraction, the same order of operations as school math:
| Operator | Operation | Precedence |
|---|---|---|
*, / | multiply, divide | higher, evaluated first |
+, - | add, subtract | lower, evaluated after |
Applying a discount needs parentheses to force the subtraction to happen first: (1 - discount_pct / 100.0) before multiplying by price.
Before you run it — Charger has a 0% discount. What line_total do you expect for it, unchanged from the subtotal?
The integer division trap
discount_pct is stored as a whole number (10 means 10%). Dividing two integers in SQL does integer division: it truncates the result to a whole number, throwing away everything after the decimal point. That's why the earlier example divides by 100.0 (a decimal literal) instead of 100: it forces the whole expression into decimal math.
Before you run it — 10 / 100 as integers versus 10 / 100.0 as decimals. What do you think each one returns?
Why this bug is expensive in practice
Integer division is the single most common "why is my answer 0" bug in interview SQL, and it's dangerous for the same reason as the NULL trap earlier: it doesn't error, it just quietly returns a wrong number. A conversion-rate or discount calculation that silently divides two integers can ship a real dashboard showing 0% everywhere, and nothing about the query looked broken. If a ratio or percentage comes out suspiciously flat or wrong, check whether one side of the division is quietly an integer.
If you've used Excel or Google Sheets
This is actually one place SQL is less forgiving than a spreadsheet. Excel and Sheets always divide as decimals (=10/100 gives 0.1 without any extra thought), so the integer-division trap has no real equivalent there. It's worth remembering specifically because it will not match your spreadsheet intuition.
Ready to practice? Traveloka's booking totals question below applies this exact discount-calculation pattern.
Next: a handful of functions for rounding and reshaping numbers, including a couple that clean up exactly the kind of result these arithmetic examples produce.