Feanor Feanor - 5 months ago 10
SQL Question

Making a sum of one column of child table

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';


And I have the query to get the sum of all orders linked to a particular id:

SELECT SUM(amount) FROM order WHERE deliveries_id = 7144;


What I can't seem to figure out is the query needed to return a list of [delivery_id, sum_order_amounts] for all deliveries that happened on that particular day, SQL is not my strongest point. >_>

Can anyone help me out? Thanks in advance!

Answer

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