MYSQL Count group by rows ignoring effect of JOIN and SUM fields on Joined tables

I have 3 t开发者_如何学JAVAables:

Orders
- id
- customer_id

Details
- id
- order_id
- product_id
- ordered_qty

Parcels
- id
- detail_id
- batch_code
- picked_qty

Orders have multiple Details rows, a detail row per product.

A detail row has multiple parcels, as 10'000 ordered qty may come from 6 different batches, so goods from batches are packed and shipped separately. The picked quantity put in each parcel for a detail row should then be the same as the ordered_qty.

... hope that makes sense.

Im struggling to write a query to provide summary information of all of this.

I need to Group By customer_id to provide a row of data per customer.

That row should contain

• Their total number of orders
• Their total ordered_qty of goods across all orders
• Their total picked_qty of goods across all orders

I can get the first one with:

SELECT customer_id, COUNT(*) as number_of_orders 
FROM Orders 
GROUP BY Orders.customer_id

But when I LEFT JOIN the other two tables and add the

SELECT ..... SUM(Details.ordered_qty) AS total_qty_ordered, 
SUM(Parcels.picked_qty) AS total_qty_picked

.. then I get results that dont seem to add up for the quantities, and the COUNT(*) seems to include the additional lines from the JOIN which obviously then isn't giving me the number of Orders anymore.

Not sure what to try next. ===== EDIT =======

Here's the query I tried:

SELECT 
  customer_id, 
  COUNT(*) as number_of_orders, 
  SUM(Details.ordered_qty) AS total_qty_ordered, 
  SUM(Parcels.picked_qty) AS total_qty_picked 
FROM Orders
LEFT JOIN Details ON Details.order_id=Order.id
LEFT JOIN Parcels ON Parcels.detail_id=Detail.id 
GROUP BY Orders.customer_id

---

try COUNT(distinct Orders.order_id) as number_of_orders, as in

SELECT 
  customer_id, 
  COUNT(distinct Orders.order_id) as number_of_orders, 
  SUM(Details.ordered_qty) AS total_qty_ordered, 
  (select SUM(Parcels.picked_qty) 
    FROM Parcels WHERE Parcels.detail_id=Detail.id ) AS total_qty_picked 
FROM Orders
LEFT JOIN Details ON Details.order_id=Order.id    
GROUP BY Orders.customer_id

EDIT: added an other select with subselect

---

Is there any particular reason you feel the need to combine all these in one query? Simplify by breaking it up in to separate queries, and if you want a single call to get the results, put the queries in a stored procedure, using temp tables.