Preventing Fatality Rate Errors Caused by Integer Division in SQL

by

Summary

The query calculates a fatality‑rate per 100 k population, but the division order and integer math produce an incorrect result. By casting to a floating‑point type and applying the correct arithmetic order, the rates match the expected values (e.g., Wyoming ≈ 24.66).

Root Cause

  • Integer division: fatalities / population truncates the fraction before scaling.
  • Incorrect order of operations: multiplying after integer division yields a wrong scale.
  • Missing cast to a decimal type, so the database defaults to integer arithmetic.

Why This Happens in Real Systems

  • Many RDBMS (PostgreSQL, MySQL, SQL Server, Oracle) default to integer arithmetic when both operands are integers.
  • Developers often assume implicit conversion to float, which does not happen unless explicitly requested.
  • Legacy schemas store counts as INT; when combined in calculations, the result is also INT unless cast.

Real-World Impact

  • Misleading analytics: policy decisions based on faulty fatality rates.
  • Incorrect dashboards: users lose trust in reporting tools.
  • Downstream calculations: any metric derived from the rate propagates the error.
  • Regulatory compliance: reports submitted to authorities may be rejected.

Example or Code (if necessary and relevant)

SELECT
    state,
    fatalities,
    population,
    CAST(fatalities AS DECIMAL(10,2)) / population * 100000 AS fatality_rate_per_100k
FROM accidents;

How Senior Engineers Fix It

  • Always cast at least one operand to a non‑integer type before division.
  • Apply scaling after the division to keep precision (divide first, then multiply).
  • Add explicit rounding for presentation (ROUND(..., 2)).
  • Write unit tests that compare query output against known correct values.
  • Document the intended data type in code reviews and schema comments.

Why Juniors Miss It

  • They expect automatic type promotion like in high‑level programming languages.
  • They often *write quick “SELECT …/…100000”** without checking the intermediate data types.
  • Lack of experience with SQL’s type system and how different engines handle integer math.
  • Missing peer review or test coverage that would surface the rounding error early.

Leave a Comment