Why Does Integer Division in SQL Return 0 Instead of a Decimal Value?

Understanding Why SQL Integer Division Returns Zero

Unexpected zero results from SQL division operations can be confusing. This article addresses a situation where dividing two numeric variables (@set1 and @set2) produces 0 instead of the expected 0.073667712.

The problem stems from the variables' data types. SQL variables are typically integers or strings. When @set1 (47) and @set2 (638) are treated as integers, integer division occurs, resulting in 0. To get the correct decimal result, explicitly cast these variables to floating-point numbers before the calculation.

The solution uses the CAST function to convert the variables to floats:

<code class="language-sql">SET @weight = CAST(@set1 AS FLOAT) / CAST(@set2 AS FLOAT);</code>

Casting @set1 and @set2 as FLOAT ensures floating-point division, yielding the accurate decimal value of 0.073667712. This simple change guarantees the correct mathematical outcome.

The above is the detailed content of Why Does Integer Division in SQL Return 0 Instead of a Decimal Value?. For more information, please follow other related articles on the PHP Chinese website!