Integer division and rounding surprises

In Postgres and SQL Server, 5/2 is 2, because integer divided by integer gives an integer. The fractional part is cut off, not rounded. To get 2.5, make at least one side a decimal.

Behaviour by engine

• Postgres, SQL Server: integer / integer returns an integer, so 5/2 = 2.
• BigQuery: / always returns FLOAT64, so 5/2 = 2.5. Use DIV(5, 2) for integer division.
• MySQL: / returns a decimal, and the keyword DIV is for integer division.
• Snowflake: / between integers returns a decimal, so 2.5.

Do not rely on remembering which engine does what. Write the cast so it works on all of them.

Calculating a percentage

-- Wrong in Postgres: returns 0 for 40 paid out of 100 orders
SELECT paid_orders / total_orders * 100 FROM stats;

-- Right: force decimal math, multiply before rounding
SELECT ROUND(100.0 * paid_orders / NULLIF(total_orders, 0), 2) AS paid_pct
FROM stats;

100.0 * goes first, so the whole expression is decimal. Putting the multiplier at the start also avoids a case where paid_orders / total_orders is computed first as integer and then multiplied, which still gives 0.

Divide by zero

A day with zero orders makes paid / total fail in most engines. NULLIF(total_orders, 0) turns a zero denominator into NULL, and the result becomes NULL instead of an error. BigQuery also has SAFE_DIVIDE(a, b), which returns NULL when b is 0. Whether a NULL percentage should display as 0 or stay blank is a reporting decision, so make it on purpose with COALESCE.

ROUND goes last. Rounding intermediate values and then adding them up drifts away from the true total.