Numeric functions

ROUND, CEIL, FLOOR, ABS, and integer division.

What you'll be able to do

Round and truncate numbers
Avoid the integer division trap
Handle division by zero

Rounding

ROUND(19.567, 2)   -- 19.57
CEIL(19.1)         -- 20   (CEILING in SQL Server)
FLOOR(19.9)        -- 19
ABS(-42)           -- 42
MOD(10, 3)         -- 1    (or 10 % 3)

Integer division

The trap that quietly produces wrong percentages:

SELECT 7 / 2;        -- 3 in Postgres and SQL Server, not 3.5

When both operands are integers, many databases perform integer division and discard the remainder. Cast one side:

SELECT 7::numeric / 2;        -- Postgres
SELECT CAST(7 AS FLOAT) / 2;  -- portable

A ratio of counts is the classic victim — COUNT(a) / COUNT(b) returns 0 far more often than it should.

Division by zero

total / NULLIF(order_count, 0)

NULLIF returns null when the denominator is zero, so the expression yields null instead of raising an error. Wrap with COALESCE if you want a displayed zero.

Percentages

ROUND(100.0 * shipped / NULLIF(total, 0), 1) AS pct_shipped

Writing 100.0 rather than 100 forces floating-point arithmetic, which sidesteps integer division in one character.

Key points

Integer / integer performs integer division in many dialects
Use NULLIF on the denominator to avoid division-by-zero errors
Multiply by 100.0 rather than 100 to force decimal arithmetic

Check yourself