Why does AVG(score) return 4 rather than 4.5 when score is an INTEGER column?

## The symptom A table holds two exam scores in an `INTEGER` column, 4 and 5. On some engines this query returns 4.5 and on others it returns 4: ```sql SELECT AVG(score) FROM exam_results; ``` Nothing is wrong with the data and nothing is wrong with the query. The difference is a **result-type rule**, and it catches people because averaging is the one aggregate whose natural answer usually is not the type you fed it. ## Why the type rule bites Standard SQL specifies that if the argument of `AVG` is an exact numeric type, the result is an exact numeric type — but it leaves the precision and scale of that result **implementation-defined**. Two readings are therefore both conforming: - Promote the result to a type with a fractional part (a decimal or numeric), and report 4.5. - Keep the argument's own type, INTEGER, and report the integer part of the true average: 4. The second reading is not a bug. It is the same principle as integer division in most programming languages: an operation over integers produces an integer, discarding the remainder. Because the truncation happens *inside* the aggregate, no rounding or warning surfaces — the value simply arrives with the decimals gone. ## Who does what Engines genuinely diverge here, so this is one of the few places worth memorising concrete behaviour: - **SQL Server** returns the argument's type for integer input, so `AVG` over an `int` column is an `int` and truncates. - **PostgreSQL** returns `numeric` for integer input, so you get `4.5000000000000000`. - **MySQL** returns a `DECIMAL` for exact-valued arguments, so you get `4.5000`. - **SQLite** is dynamically typed and its `avg()` always yields a floating-point value, so you get 4.5. If a query is meant to run on more than one of those, do not rely on any of it — make the input unambiguous. ## Fixing it: cast the input, not the result The correct fix widens the operand before the aggregate consumes it: ```sql SELECT AVG(CAST(score AS DECIMAL(10,2))) FROM exam_results; -- 4.50 ``` Multiplying by a decimal literal has the same effect and is shorter, though slightly less explicit: ```sql SELECT AVG(score * 1.0) FROM exam_results; ``` The tempting but wrong fix is to cast the **result**: ```sql SELECT CAST(AVG(score) AS DECIMAL(10,2)) FROM exam_results; -- 4.00, not 4.50 ``` By the time the outer `CAST` runs, `AVG` has already produced the integer 4; converting 4 to a decimal gives 4.00. Precision that was discarded inside the aggregate cannot be recovered outside it. This is the single most common wrong answer to the question, and interviewers ask it specifically to see whether you understand *where* the loss occurs. The same trap appears when people write the average manually: ```sql SELECT SUM(score) / COUNT(score) FROM exam_results; -- integer / integer SELECT SUM(score) * 1.0 / COUNT(score) FROM exam_results; -- fractional ``` If both operands are integers, the division is integer division, whatever the engine does inside `AVG`. ## Choosing the target type When you cast, pick the type that matches the meaning of the number. `DECIMAL`/`NUMERIC` with an explicit precision and scale gives exact base-10 arithmetic and is the right choice for money and for anything a human will reconcile. Casting to `DOUBLE PRECISION` avoids truncation too, but introduces binary floating-point rounding, so an average of currency amounts may print a trailing `.30000000000000004`. Choose the scale deliberately: `DECIMAL(10,2)` says "two decimal places matter", which is a statement about the business, not about SQL. ## How to answer it in an interview Say three things, in order: the result type of `AVG` is derived from the argument type and is implementation-defined in precision; engines differ, with SQL Server truncating integer input while PostgreSQL, MySQL and SQLite return a fractional type; and the portable fix is to cast the argument, because casting the result recovers nothing. If you have room for a fourth point, note that this is the *same* family of rules that makes an integer `SUM` overflow on engines that do not widen — one rule, two well-known production bugs.