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
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.
