SQL: integer division truncates before it reaches AVG

Dividing two integers gives an integer in most engines, so the fractional part is lost before any rounding you apply. Cast one side to a decimal first.

Code

SELECT 7 / 2                   AS int_div,     -- 3
       7.0 / 2                 AS dec_div,     -- 3.5
       CAST(7 AS numeric) / 2  AS cast_div;    -- 3.5

-- The bug in the wild: a percentage that is always 0 or 100
SELECT paid / total * 100            AS wrong,
       paid * 100.0 / total          AS right_way
  FROM stats;                        -- paid=3, total=4

Output

int_div | dec_div | cast_div
      3 |     3.5 |      3.5

wrong | right_way
    0 |      75.0

-- Multiplying by 100.0 before dividing fixes it without a cast, because the
-- decimal literal promotes the whole expression.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25