Using MySQL to store and query some data.
I have two tables
A (list of costs):
id, order_id, amount, rate
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
1, 69, 420, 15 2, 69, 100, 20
And the rows for
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.
Answered By – forpas
Answer Checked By – Timothy Miller (BugsFixing Admin)