Concept · SQL & Querying

SUM(a)/SUM(b) or AVG on integers truncates unless cast to a float first (dialect-dependent).

The conversion-rate query SUM(conversions)/SUM(visits) is the classic casualty — on truncating engines it returns 0 for every rate under 100%, a wrong number wearing plausible clothes. On non-truncating engines the same text is fine until it's ported, which makes this dialect-class: the bug is in the migration, not the query. The armor is unconditional: 1.0 *, ::FLOAT, or CAST one operand.

See it break

hits	views
3	7
4	7

The trap

SELECT
  SUM(hits) / SUM(views) AS rate
FROM
  r;

The fix

SELECT
  1.0 * SUM(hits) / SUM(views) AS rate
FROM
  r;

The trap

0.5

The fix

0.5

In DuckDB SUM(hits)/SUM(views) float-divides to 0.5, but on Postgres/SQL Server the same integer division truncates to 0 — every rate under 100% reads as 'no conversions'; casting one side (1.0 *) is correct on every engine.

The rule

Cast one side always in ratios; // or FLOOR when truncation is the point.

Postgres/SQL Server truncate integer division; DuckDB/MySQL float-divide; // is explicit integer division in DuckDB.

Shows up elsewhere

Implicit casts in comparisons Average of averages

Try one →Prove it in practice →