Why does dividing two whole numbers ever return anything but a normal number?

Because the columns are stored as integer types, and integer division in Postgres and SQL Server returns an integer result, discarding any remainder. 3 / 10 as integer division is 0 (three tenths, rounded down to the nearest whole number), not 0.3. It's not a rounding bug — it's what integer division is defined to do.

So MySQL just doesn't have this problem?

MySQL's / operator returns a decimal result even for two integer operands, by default — so 3 / 10 in MySQL is genuinely 0.3000, no cast required. That's actually part of what makes this bug so easy to miss: the same-looking query is correct on MySQL and silently wrong on Postgres or SQL Server, with no error either way.

Why does it matter which side I cast, or does it matter at all?

Casting just one side to a decimal/float type is enough — SQL promotes the whole expression to that wider type once either operand isn't a plain integer. Casting both is harmless but unnecessary; the common mistake is casting neither.

Is there a case where this happens without an obvious division sign?

Yes — AVG() over an integer column in some databases/contexts can be affected by the same integer-arithmetic path depending on how intermediate values are computed, and percentage calculations built as (COUNT(...) / COUNT(*)) * 100 hit this constantly since both counts are integers.

How do I catch this before shipping a report?

Any time a percentage or rate comes back as a suspiciously round number — especially exactly 0 or 100 — with a numerator and denominator you know aren't that extreme, suspect integer division first. Sanity-check against the raw counts: 3 converted out of 10 signups should never actually display as 0%.

SQL Integer Division Returns 0 | Percentage Bug

You ask for the conversion rate — trials to paid, this month. It comes back 0. You know it isn't zero; you can count three or four conversions by eye. The query ran without error, the counts it's built from look right individually, and the final percentage is just... wrong, flatly and confidently wrong, at 0.

This is almost always integer division, and whether you even hit it depends on which database you're using — the same-looking query is fine on one and silently broken on another.

Division that throws away the remainder

In PostgreSQL and SQL Server, dividing an integer column by an integer column (or an integer literal) returns an integer result — the fractional part is discarded, not rounded, just dropped:

SELECT 3 / 10;   -- returns 0, not 0.3

This isn't a bug in the database; it's what integer division is defined to do, in the same family of behavior as most programming languages' int / int. The problem is that a percentage calculation built the obvious way runs straight into it:

SELECT (COUNT(*) FILTER (WHERE converted) / COUNT(*)) * 100 AS conversion_rate
FROM trials

Both COUNT(*) results are integers. 3 / 10 truncates to 0 before the * 100 ever gets a chance — so the whole expression becomes 0 * 100 = 0, a confident, wrong, exactly-round zero.

Why MySQL makes this more confusing, not less

MySQL's / operator returns a decimal result by default even for two integer operands — 3 / 10 in MySQL is genuinely 0.3000, no cast needed. That means the identical-looking query is correct on MySQL and silently wrong on Postgres or SQL Server. If a query was written or tested against one database and then pointed at another, this is exactly the kind of thing that breaks with zero visible difference in the SQL text.

How to catch it

The signature is a rate or percentage landing on a suspiciously clean number — 0, 100, sometimes a plain whole number where you'd expect a decimal — combined with knowing the raw counts aren't actually that extreme. Sanity-check the two counts directly: SELECT COUNT(*) FILTER (WHERE converted), COUNT(*) FROM trials — if those are 3 and 10, and the "rate" came back 0, that mismatch is the whole bug.

The fix: cast before you divide

Casting either side of the division to a decimal or float type is enough — the whole expression gets promoted once one operand isn't a plain integer:

-- Postgres
SELECT (COUNT(*) FILTER (WHERE converted)::numeric / COUNT(*)) * 100 AS conversion_rate

-- SQL Server
SELECT (CAST(COUNT(CASE WHEN converted = 1 THEN 1 END) AS FLOAT) / COUNT(*)) * 100 AS conversion_rate

Casting both sides is harmless but unnecessary — the mistake worth avoiding is casting neither.

The prompt that heads this off

When computing a rate, ratio, or percentage from two integer counts,
cast at least one side to numeric/decimal/float before dividing —
integer division truncates the result to a whole number in Postgres
and SQL Server (MySQL doesn't have this issue by default). If a
percentage comes back as exactly 0 or 100, check the raw counts before
trusting it.

The short version

3 / 10 being 0 isn't a rounding quirk or an edge case — it's the literal, documented behavior of dividing two integers in most databases, and a percentage calculation is exactly the shape of expression most likely to hit it without anyone noticing until the number looks obviously wrong.

Read-only MCP gateway: mcpserver.design.

Related reading: