thebench.bio #1 Ecosystem for the Bio-Tech Industry
All SQL questions

Question 11 of 20 Fundamentals Interpretation

Why can a percentage calculated in SQL come out as zero?

Short answer

Dividing one integer by another in PostgreSQL truncates toward zero, so 37 / 120 returns 0. COUNT returns an integer type, so a failure rate built from two counts needs one side cast to numeric before dividing.

Why

The PostgreSQL documentation for the division operator says that for integral types, division truncates the result toward zero, and gives 5 / 2 = 2 as the example. COUNT(*) returns bigint, so COUNT(*) FILTER (WHERE status = 'fail') / COUNT(*) is integer division. With 37 failed runs out of 120, the query reports 0, which reads as a perfect run.

Multiplying by 100 first gives 100 * 37 / 120 = 30, which looks plausible but has silently lost 0.83 percentage points. The fix is to make one operand non-integer before the division, for example 100.0 * failed / total or failed::numeric / total, and then round. round(v, s) with a number of decimal places is defined for numeric, so round(100.0 * 37 / 120, 1) gives 30.8. Division by zero is the second trap.

A group with no runs makes the query fail, so wrap the denominator in NULLIF(total, 0), which returns NULL instead of an error and makes the empty group visible. Other databases differ, which is why the same query can give 30.8 in one engine and 0 in another. Read the dialect's rules rather than assuming.

A strong answer covers

  • Integer division truncates toward zero
  • COUNT returns an integer type
  • Cast to numeric before dividing
  • Guard the denominator with NULLIF
  • Behavior differs between database engines

Common mistakes

  • Multiplying by 100 to avoid a zero result and not noticing the remaining truncation
  • Rounding after integer division, which rounds a value already truncated
  • Assuming a query ported from another database keeps the same division behavior