1

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
1

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);
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.