CODEWITHSUNDEEP
mysql
Lanoye
Lanoye

Reputation: 13

Get the total price of an order

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.

Upvotes: 0

Views: 223

Answers (1)

Jim Wright
Jim Wright

Reputation: 6058

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;

Upvotes: 1

Related Questions