How do I avoid an integer result when dividing aggregate totals?
Quick answer
In SQLite, dividing two integer expressions can use integer division, which truncates toward zero. Make one operand REAL before dividing—for example, 1.0 * SUM(amount) / SUM(quantity)—so the ratio can include a fractional part. Check separately that the denominator is not zero and account for possible NULL values; converting an operand does not settle those cases. [S1]
The question, the details, the next step.
I’m dividing two totals in SQLite to get an average amount per item, but I’m getting a whole number. How should I approach this?
If both aggregate results are integers, SQLite uses integer division when there is no overflow, truncating the fractional part toward zero. Make an operand REAL before the division, such as by multiplying the numerator by 1.0: [S1]
SELECT 1.0 * SUM(amount) / SUM(quantity) AS average_amount
FROM line_items;Here, the assumed columns are amount and quantity, and the intended measure is total amount divided by total quantity. The conversion changes the arithmetic used for the ratio; it does not change what your totals mean. [S1]
Can you show what the integer result looks like with some small example totals?
Assume two rows have amounts of 199 and 500 units, and quantities of 2 and 3 items. These are invented figures, and they illustrate the arithmetic only:
- Total amount:
199 + 500 = 699. - Total quantity:
2 + 3 = 5. - Integer expression:
SUM(amount) / SUM(quantity)gives699 / 5, or139under SQLite integer division. - Fractional expression:
1.0 * SUM(amount) / SUM(quantity)gives139.8.
The second result is the total amount per item for these assumed rows. It is not an average of the two row-level amounts; it divides the combined amount by the combined quantity. That distinction matters when rows represent different numbers of items.
Would casting the totals work too, and where should I put it?
The key is to make at least one operand REAL before the division. One clear way to do that is to multiply the numerator by 1.0:
SELECT 1.0 * SUM(amount) / SUM(quantity) AS average_amount
FROM line_items;This puts the REAL operand in the division expression, after the aggregate is written. The documented example uses this form to request a fractional result. [S1] Check that it matches your intended calculation: it divides the combined amount by the combined quantity.
What should I check before relying on the result?
Check that the numerator and denominator represent the quantities you actually intend to compare. Also check the denominator: an empty or zero denominator needs separate handling, and NULL operands can affect the result. [S1] The REAL conversion fixes integer division, but it does not decide how your application should represent a missing or undefined ratio. [S1]
A reusable review sequence is:
- Confirm what each aggregate measures and its units.
- Verify the summed denominator is nonzero for the rows being calculated.
- Decide how your query or application should handle NULL values and empty input.
- Make an operand REAL before dividing, for example with
1.0 *. - Compare a test result with a small hand-calculated example.
For the invented example, the hand check is 699 / 5 = 139.8. If the query instead returns 139, inspect the expression’s operand types and conversion placement.
A practical checklist
Confirm that the two aggregate totals have the intended meaning and units.
Convert an operand to REAL before division, such as with
1.0 * SUM(amount).Check whether the denominator can be zero or whether the input can be empty.
Decide how NULL values should affect the result.
Compare a test result with a small hand-calculated example.
A few more useful answers
Does multiplying by 1.0 change the aggregate total?
It makes that operand REAL for the division expression, allowing a fractional result instead of integer division. [S1]
Will the fractional expression handle a zero denominator?
No. SQLite’s documented fractional-expression example does not remove the need to handle an empty or zero denominator separately. [S1]
Is total amount divided by total quantity always the average I need?
Not necessarily. It gives the combined amount per combined quantity; confirm that this is the measure your task requires.
Sources
References used for this guide.
- [S1] SQLite SQL Language Expressions sqlite.org · Checked