Using MySQL to store and query some data. I have two tables
Table A (list of costs):
id, order_id, amount, rate
Table B (list of finalized orders):
id, order_id, total, rate
Rate in both tables are percents. For every finalized order row, there are many rows of costs in A. The behavior I'm looking for is to output the sum of profit of all rows in B which include the costs of A.
Assuming the following rows for A:
1, 69, 420, 15
2, 69, 100, 20
And the rows for B:
1, 69, 1000, 10
2, 70, 500, 30
The math would look something like
((1000 - (420 * 15 / 100) - (100 * 20 / 100)) * 10 / 100)
+
(500 * 30 / 100)
= 241.7
I can probably get this done with subqueries, but I'm afraid it won't be very fast with loads of rows in B that each have 0-30 rows in A associated, and it's going to be a query that happens often.
Any help is appreciated and if something needs clarification let me know ! :)
Use 2 levels of aggregation:
SELECT SUM((b.total - COALESCE(a.amount, 0)) * b.rate) / 100 total_profit
FROM tableB b
LEFT JOIN (
SELECT order_id, SUM(amount * rate) / 100 amount
FROM tableA
GROUP BY order_id
) a ON a.order_id = b.order_id;
See the demo.