Mysql – One to many join, Sum from both table

MySQLmysql-5.1mysql-5.5

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..

Best Answer

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);
Related Question