Joining two Tables and summing columns in both-CodePudding

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 ! :)

CodePudding user response：

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.