Apologies in advance if this question has been asked before, I tried searching for it but I'm afraid I might be using the wrong terminology.
I have one table deliveries and a delivery can contain several orders. The orders table contains the amount to be paid for that specific order.
What I'd like is one query that returns a list of delivery ids and the total amount to be paid for all orders linked to that delivery for all deliveries on a certain day.
I have the query to get all delivery ids for deliveries in a particular day:
SELECT id FROM delivery WHERE delivered_on BETWEEN '2016-6-10 00:00:00' AND '2016-6-10 23:59:59';
SELECT SUM(amount) FROM order WHERE deliveries_id = 7144;
Did you want this?
SELECT d.id AS delivery_id, SUM(o.amount) AS sum_order_amounts FROM delivery d LEFT JOIN `order` o ON d.id = o.deliveries_id WHERE d.delivered_on BETWEEN '2016-6-10 00:00:00' AND '2016-6-10 23:59:59' GROUP BY d.id