I had a request from a customer to solve an issue of repeating values and correct totals.
This issue is better solved in a correct data model, but lets go for the quick and dirty aproach.
We have a table:
- Order Number
- Order total
- Payment Number
- Payment Sum
Since they are usually several payments for each Order, the Order total is repeating, and we have wrong totals:
For Order 100 we have a total for Order Total of 400 instead of 200, for 110 it's 900 instead of 300. The total should be 500, but it's 1300.
What should I do?
In the Order Total column formula I'll change it from "ORDER_PAYMENTS"."Order Total" to AVG("ORDER_PAYMENTS"."Order Total" by "ORDER_PAYMENTS"."ORDER_NUMBER") (max or min are as good).
The result has correct totals for each order, but wrong grand total.
So the last part, is to change the aggregation rule of the problematic column to sum:
Now all the totals are correct.