Nsryjdtyk

I need to calculate the total price of an order.

I am using this setup:

Orders

order_id

--------

1

2

3

Picklists

picklist_id order_id

----------- --------

1           1

2           1

3           2

4           3

Products

product_id picklist_id name           price amount

---------- ----------- -------------- ----- ------

1          1           product A      100   1

2          1           product B      200   3

3          2           product C      300   2

4          3           product A      100   2

5          4           Product C      300   1

My question is, how do I calculate the total price for an order

So my outcome, ideally, would be something like this:

order_id total_price

-------- -----------

1        1200

2        200

3        300

EDIT:

Query so far:

SELECT

order_picklists.name,

FORMAT(SUM(price * amount), 2) AS total

FROM order_products

JOIN order_picklists ON order_picklists.id = order_products.picklist_id

GROUP BY order_picklists.name

This, however, only adds everything per picklist, but I need those combined for the order.

You should GROUP BY the order_id which will return only 1 row per order.

SELECT o.order_id, SUM(p.price * p.amount) AS total

FROM orders o

LEFT JOIN picklists pl ON pl.order_id = o.order_id

LEFT JOIN products p ON pl.picklist_id = p.picklist_id

GROUP BY o.order_id;