Chapter 10 · basic · 6 min
Math functions: ROUND, ABS, CEIL, FLOOR
Raw arithmetic often needs one more step before it's presentable: rounding to a sane number of decimal places, or forcing a value up or down to satisfy a business rule. A small set of functions covers almost every case; the discounted totals from the last chapter are a perfect example of numbers that need cleaning up before anyone should see them.
The dataset
The same order_items table from the previous chapter.
Schema
| id | INT |
| product | TEXT |
| quantity | INT |
| unit_price | NUMERIC |
| discount_pct | INT |
Example data
ROUND: control the decimal places
ROUND(value, n) rounds to n decimal places, essential for money, where you don't want RM 40.545000001 showing up on an invoice.
Before you run it — same discount math as last chapter, just rounded to 2 places now. What do you expect for Earbuds?
CEIL and FLOOR: rounding in one direction
CEIL(x) always rounds up to the next whole number; FLOOR(x) always rounds down. Neither behaves like ordinary rounding: CEIL(10.01) still becomes 11. Reach for these whenever a business rule insists on a direction, like billing a fare up to the next ringgit or awarding loyalty points rounded down.
Before you run it — CEIL(10.2) and FLOOR(10.8). Remember neither rounds to nearest. What do you expect?
ABS: distance from zero
ABS(x) strips a negative sign, returning the value's distance from zero. Handy whenever a column can be negative (a refund, an adjustment, a variance) but you only care about the magnitude, e.g. "how far off was the forecast" regardless of whether it was over or under.
Before you run it — what does ABS(-42) return?
The cheat sheet
| Function | Direction | Example | Result |
|---|---|---|---|
ROUND(x, n) | nearest, to n decimals | ROUND(10.556, 2) | 10.56 |
CEIL(x) | always up | CEIL(10.01) | 11 |
FLOOR(x) | always down | FLOOR(10.99) | 10 |
ABS(x) | n/a, magnitude only | ABS(-42) | 42 |
Getting the direction wrong is a real business bug, not just a cosmetic one: rounding a fare down instead of up (or vice versa) is a real, quantifiable revenue difference at scale. That's why ROUND and CEIL/FLOOR are separate functions instead of one "round" that guesses.
If you've used Excel or Google Sheets
These map almost one-to-one: ROUND() has the identical name and argument order in both. Excel/Sheets' CEILING() and FLOOR() do the same job as SQL's CEIL/FLOOR, just with a slightly longer name for the first one. ABS() is identical in every tool that has it.
Ready to practice? Mr DIY's price rounding question below is built around choosing the correct direction.
Between arithmetic operators and this small set of functions, you can compute and present almost any number an interview question asks for. Next: turning raw values into labels with CASE WHEN.