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.
The basic operators
+, -, *, / work exactly like you'd expect, directly on columns. This computes a subtotal for each line before any discount:
| product | quantity | unit_price | subtotal |
|---|---|---|---|
| Phone Case | 3 | 15.00 | 45.00 |
| Charger | 2 | 45.00 | 90.00 |
| Earbuds | 5 | 89.00 | 445.00 |
Quantity times unit price
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.
| product | line_total |
|---|---|
| Phone Case | 40.50 |
| Charger | 90.00 |
| Earbuds | 356.00 |
Discounted total, correctly parenthesized
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:
| integer_division | decimal_division |
|---|---|
| 0 | 0.1 |
10 / 100 is 0, not 0.1! That's why the earlier example divides by 100.0 (a decimal literal) instead of 100: it forces the whole expression into decimal math.
Same numbers, very different answers
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.