Unexpected result with subtraction in aggregate function

Why do i get different sums

SELECT SUM(`amount` - `paid_amount`), SUM(`amount`) - sum(`paid_amount`) FROM `invoice` 

In both cases i expect to get equal amounts but i don’t

Add Comment
1 Answer(s)

The first sum will only include rows where amount and paid_amount are not null, because - returns null if either operand is null and sum returns the sum of all non-null values.

If there are no null values involved and you are seeing a slight difference between the two, you likely are using float or double type columns instead of decimal types. Try casting to a decimal type inside the sum.

Answered on July 16, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.