SQL, data modeling, data quality, EDA, visualization · insight-0067
Cast to a decimal type before dividing integer counts in SQL
- Evidence type
- documented fact
- Difficulty
- easy
- Roles
- Data analyst, Data scientist
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A conversion-rate query divides COUNT of bookings by COUNT of searches and returns 0 for every route.
Action
Multiply by 1.0 or CAST one operand to REAL or NUMERIC before dividing, and guard the denominator with NULLIF(denominator, 0). Add a test case with a known fractional result.
Reason
In SQLite and several other engines, dividing two integers performs integer arithmetic; a real operand switches to floating point.
Boundary
Rates, shares, averages computed manually and any ratio of counts.
Symptom or risk
Integer division truncates, so rates below 1 become 0 and percentages lose precision, often unnoticed in an aggregate chart.
Exceptions
AVG already returns a floating-point value in SQLite; the issue is explicit division of integers.
Trade-offs
Floating point introduces rounding; use NUMERIC types for money.
Pitfalls
Porting a query between engines with different division semantics without re-testing.
Lessons and concepts
Claims and their sources
In SQLite, the / operator on two integers uses integer arithmetic; if either operand is real, floating-point arithmetic is used.
SQLite avg() returns a floating-point value whenever there is at least one non-NULL input, even if all inputs are integers.