# One to many join, Sum from both table

I have two tables: orders and ordersdetail...(partial table)

`orders- id(primary key), amount, date( index)`

`orderdeails- id(primary key), skunitid, quantity, order_id`

Each order has multiple order details.

Query:

``````SELECT Sum(orders.amount), SUM(ordersdetails.quantity)
FROM orders joins orderdetails on ordersdetail.order_id = orders.id
WHERE {{conditions on date column of order table}}
``````

I know its the wrong query, but I want data from both tables in single query (single iteration). When I use a join, the order table's amount comes so many times. How I can get the sum of amount and sum of quantity in single query (I have an index on date column in the order table which has million rows).

I need an efficient way..

• At what level do you need the summation? Are you interested in a general sum, a sum by order, a sum by date or some other level? – Thomas Cleberg Dec 12 '14 at 20:18
• i have given date range...i have to find sum.. – Bhupendra Pandey Dec 12 '14 at 20:19

Suppose your date range is `2014-12-08` to `2014-12-11`

This would be the Query you need

``````SELECT B.*,A.quantity
FROM
(
SELECT
AA.id,SUM(BB.quantity) quantity
FROM
(
SELECT id FROM orders
WHERE date >= '2014-12-08' AND date <= '2014-12-11'
) AA INNER JOIN orderdetails BB ON AA.id = BB.order_id
GROUP BY AA.id
) A INNER JOIN orders B USING (id);
``````

This should work, takes maximum use of your date index and minimizes possible join anomalies:

```SELECT (SELECT SUM(orders.amount) FROM orders WHERE orders.date >= [BEGIN DATE] and orders.date < [END DATE) amount, (SELECT SUM(od.quantitiy) FROM ordersdetails od INNER JOIN orders o ON od.id = o.id AND o.date >= [BEGIN DATE] and o.date < [END DATE) amount) quantity ```