Integer division returns 0 (or truncates)

7 / 2 comes back 3, not 3.5, on every major engine — integer divided by integer stays an integer. Not a bug, but the single most common silent-wrong-result mistake in SQL.

There's no error text to quote on this page — that's the problem. Every major engine agrees on this one, silently:

Loading editor…

3, not 3.5. When both operands of / are integers — integer columns or integer literals — the result stays an integer, truncated toward zero, on Postgres, MySQL, SQL Server, and SQLite alike. This is standard SQL behavior, not an engine quirk, and it produces no warning anywhere.

Where it actually bites

Real division almost never looks like a literal 7 / 2 — it's usually a ratio computed from integer columns, and it reads correctly at a glance:

Loading editor…

salary is an integer column; 12 is an integer literal — so the result truncates every row's fractional cents, silently. The bug hides especially well when it sometimes comes out even by coincidence, so a few rows of manual testing don't catch it.

The fix — force one operand to be non-integer

Cast either side, or multiply by a decimal literal — either makes the whole expression real/float arithmetic:

Loading editor…

Applied to the earlier query:

Loading editor…

Watch for it inside aggregates too

SUM(a) / COUNT(*) has the exact same trap if both sides happen to be integer-typed — cast the division, not the inputs to SUM:

Loading editor…

Every department's average lands on a suspiciously round number — because it's silently floored. The corrected version:

Loading editor…