MYSQL Count group by rows ignoring effect of JOIN and SUM fields on Joined tables
I have 3 tables:

    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.

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

I have 3 tables:

    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.
