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.

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:

product	line_total
Phone Case	40.50
Charger	90.00
Earbuds	356.00

Cleanly rounded to 2 decimal places

Editable, try changing it

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:

rounded_up	rounded_down
11	10

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.

Two directions, never the 'nearest' one

Editable, try changing it

ABS: distance from zero

ABS(x) strips a negative sign, returning the value's distance from zero:

absolute_value
42

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.

Always non-negative

Editable, try changing it

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? GoCar's fare 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.