SQL integer division returns 0 instead of a decimal

The grading dashboard listed most of a class at a 0% pass rate, including a student who had cleared three of the four unit tests that morning. One row over, the student who passed all five read as a lonely 1 in a column of zeros. The pass rates were not off by a rounding error; they were integers wearing the costume of fractions.

Here is the query behind the column, meant to report each student's share of tests passed:

-- Per-student test report for the term.
-- (passed and total are integer columns; total is never 0)
SELECT
    student_id,
    total - passed AS failed,
    passed / total AS pass_rate,
    ROUND(100.0 * passed / total, 1) AS pass_pct,
    CASE WHEN passed >= total THEN 'complete' ELSE 'partial' END AS status
FROM results
ORDER BY pass_rate DESC;

Why does SQL integer division return 0 instead of a decimal?

Line 6 divides two integer columns, passed / total. When both operands are integers, SQL computes an integer result and truncates the remainder toward zero, so 3 / 4 is 0 rather than 0.75 and 1 / 8 is 0 too. Only the student who passed everything reads as 1. The ROUND(100.0 * ...) column one line down looks like the riskier arithmetic, but it is correct; the quiet line above it is the bug. Cast one side to a real first: CAST(passed AS REAL) / total.

An operator picks its return type from the operands, not from the answer you wanted

The / operator does not know you were after a fraction. It looks at its two inputs, sees two integers, and produces an integer, because that is the type its result is declared to have. The division runs to completion and then the fractional part is discarded, truncated toward zero and never rounded. So 3 / 4 is 0 and 7 / 4 is 1, not 2. PostgreSQL says it plainly in its mathematical functions and operators reference: division of integers "truncates the result towards zero."

The report even prints the cure a column later, which is what makes the bug so easy to walk past. ROUND(100.0 * passed / total, 1) is the busy-looking line, the one doing real arithmetic, yet it returns an honest 75.0 and 12.5. The 100.0 literal pulls the whole expression into floating point before the division runs, so that column keeps its fraction while the plainer passed / total beside it has already dropped its own. Same two integer inputs, one line apart, opposite answers.

Here is the part the top search results skate past. This truncation is not universal SQL. PostgreSQL, SQL Server, SQLite, and Oracle all truncate integer division, so passed / total misbehaves the same way on every one of them. MySQL and MariaDB do not: their / operator returns a decimal, and 3 / 4 there is 0.75. To get truncation in MySQL you have to ask for it by name with the DIV operator. The exact same query is a defect on Postgres and correct on MySQL, so a snippet copied between engines can pass every test on the machine it was written for and then rot on the one it ships to.

The reason it survives review is the data reviewers reach for. Seed a fixture where every student passed at least as many tests as the denominator, or where passed is a clean multiple of total, and the truncated result lands close enough to the fraction that nothing looks off. The student who passed everything is the worst tell: total / total is 1, an honest-looking pass rate that happens to be the one value the buggy expression gets right. Tidy test data reads as a few 1s and some 0s, and both look plausible for a rate.

Run it on the three rows the puzzle uses, (1, 3, 4), (2, 5, 5), (3, 1, 8), and the gap is stark:

student_id	passed	total	passed / total (buggy)	CAST(passed AS REAL) / total (fixed)
1	3	4	0	0.75
2	5	5	1	1.0
3	1	8	0	0.125

The 0% that closed a support ticket

Follow one number out of the database. The pass_rate column feeds a weekly progress export an academic advisor reads to flag anyone slipping. A student who passed three of four tests shows up as 0.00, indistinguishable from one who passed none. The advisor sends a warning, the student replies with a screenshot of three green checks, and the thread lands on whoever owns the pipeline. Nobody suspects the SQL, because 0 is a perfectly valid pass rate. The row does not crash and it raises no error; it simply reports a wrong number with the right type, for two-thirds of the class at once.

The fix: make one operand a real before the division happens

Give the operator a non-integer input so it commits to floating-point division:

SELECT
    student_id,
    CAST(passed AS REAL) / total AS pass_rate
FROM results;

Once one side is a real, the other is promoted and the result keeps its fractional part, so 3 / 4 becomes 0.75. passed * 1.0 / total does the same with less ceremony, and the ordering is the whole point: multiply before you divide, so a real reaches the / operator. Leave the multiplication until after and the integer division has already thrown the remainder away.

Both tempting shortcuts arrive too late. ROUND(passed / total, 2) looks like it addresses precision, but passed / total is evaluated first and returns 0 before ROUND ever sees it, so you round 0 to 0.00 and ship the same bug with two decimal places. Casting the whole result, CAST(passed / total AS REAL), fails for the identical reason: the integer division finishes inside the parentheses and hands CAST a 0 to turn into 0.0. The cast has to land on an operand, not on the result.

How to find integer division in your own queries

Grep for a slash between two bare identifiers and then read the column types at each hit: grep -rnE '\b\w+\s*/\s*\w+' --include=*.sql .. Most matches are harmless, so treat this as a prompt to check operand types, not a rule to trust blindly. Any division of two integer columns where you expected a fraction is a suspect.

While you are in there, guard the other half of the same expression. passed / total divides by zero the instant a student has no tests assigned, so wrap the denominator at the same time: CAST(passed AS REAL) / NULLIF(total, 0) fixes the truncation and turns a divide-by-zero into a NULL in one edit.

Related bugs

This is the same class of failure as AVG skipping NULL rows and reading too high and COUNT(column) counting values instead of rows: in each one the arithmetic did something defensible by the language's rules and something other than what the report assumed. More sit on the SQL bug hub, with everything else at the full write-ups.

The next time a ratio column comes back as a wall of 0s and 1s, skip the report logic and check the types feeding the slash. A pass rate that can only be 0 or 1 is not measuring a share of anything; it is quietly recording whether the student passed literally every test.