One to many join, Sum from both table

Question

I have two table orders and ordersdetail having millions of rows..

Tables:

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

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

One order have multiple order detail...

Query:

Select Sum(orders.amount),SUM(ordersdetails.quantity)
from orders join orderdetails on ordersdetail.order_id = orders.id 
where {{conditios on date column of order table}}

I knw its wrong query, I want data sum of amount and sum of quantity in single query(single iteration) but when I use join order's table amount comes so many time. I have index on date column in order table, so i must have to start query from order table (million rows in table.)

I must need efficient way..

Answer 1

Select 
   Sum(Order.Amount),
   Sum(OrderTotals.OrderQuantity)
From
   Order
Inner join
( 
   Select
       OrderDetails.OrderID,
       Sum(OrderDetails.Quantity) as OrderQuantity
   From
       OrderDetails 
   Where
       OrderDetails.OrderID in
       (Select OrderID From Order where Order.Date between @x and @y)

   Group by
       OrderDetails.OrderID
) As OrderTotals
On 
    OrderTotals.OrderID = Order.OrderID

Where
   Order.Date between @x and @y