Why can SELECT wins / games return 0 when both columns are integers, and how do you fix it?

## The rule behind the surprise SQL derives the type of an arithmetic expression from the types of its operands. When both operands of `/` are exact numerics with scale 0 — that is, integers — an engine is free to produce an exact numeric with scale 0 as well. PostgreSQL, SQL Server and SQLite do exactly that: the quotient is an integer, and everything after the decimal point is thrown away. It is **truncation toward zero**, not rounding: `7 / 2` is `3`, `3 / 4` is `0`, and `-7 / 2` is `-3`. So a perfectly reasonable-looking column, ```sql SELECT team_id, wins / games AS win_rate FROM season_stats; ``` returns `0` for every team that has not won more games than it has played — that is, for every team. The query does not error, no warning appears, and the report shows a column of zeros. ## Why it is engine-dependent This is one of the places where the majors genuinely diverge, and the divergence is the reason the bug survives code review. MySQL's `/` operator yields a fractional result even for integer operands (its integer division is spelled `DIV`), and Oracle has no separate integer type in the usual sense — numeric columns are `NUMBER`, so the quotient keeps its fraction. A query developed against MySQL and deployed against PostgreSQL will silently change meaning. Never assume; test the expression on the engine you deploy to. ## Fixing it: promote before you divide The operand types decide the result type, so change an operand: ```sql -- explicit and portable SELECT team_id, CAST(wins AS DECIMAL(10,4)) / games AS win_rate FROM season_stats; -- shorthand: multiplying by a decimal literal promotes the left side SELECT team_id, wins * 1.0 / games AS win_rate FROM season_stats; ``` Both work because once one operand is an exact numeric with scale, the whole expression is evaluated in that domain. The classic wrong fix is to cast the *result*: ```sql -- still 0: the integer division already happened inside the CAST SELECT CAST(wins / games AS DECIMAL(10,4)) AS win_rate FROM season_stats; ``` Equally wrong is `ROUND(wins / games, 4)` — rounding a value that is already `0` gives `0.0000`. Whenever you see a ratio column full of zeros or whole numbers, look at where the conversion sits relative to the operator. ## The other half of the trap: dividing by zero A ratio expression has a second failure mode. Under the standard, division by zero raises a data exception, so one team with `games = 0` aborts the whole query. MySQL is the notable exception: it returns NULL for `1/0` in a plain SELECT. Rather than depend on that, make the intent explicit: ```sql SELECT team_id, CAST(wins AS DECIMAL(10,4)) / NULLIF(games, 0) AS win_rate FROM season_stats; ``` `NULLIF(a, b)` returns NULL when `a = b` and `a` otherwise, so a zero denominator becomes NULL, and the whole ratio becomes NULL — a missing value, which is usually what a rate for a team that has played nothing actually means. If you would rather show a number, wrap the result: `COALESCE(CAST(wins AS DECIMAL(10,4)) / NULLIF(games, 0), 0)`. ## Percentages have the same shape `wins * 100 / games` looks like it avoids the problem because of the `100`, but with integer operands throughout the division is still integer division — you get a whole-number percentage, silently truncated (a 66.7% rate shows as 66). If you want decimals, promote: `wins * 100.0 / games`. ## Wider lesson The general principle is worth carrying beyond division: in SQL, the operand types determine the expression's type, and conversions applied after the fact cannot recover information the operator already discarded. When an expression mixes types, work out what type the result has before you decide it is correct. Ratios, averages of counts, and "per unit" columns are where this bites hardest, because the wrong answer is a plausible-looking number rather than an error.