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.