SQL Manager : request to export orders

[yeffouze]

Hi,

 

Trying to export all my validated orders with the SQL query below to import them into my contractor system. I need one product per line but for each line the total products quantity as shown below.

 

order id    total quantity    product   quantity

1                       5                    A              5                    

2                      10                   B              3

2                      10                   C              3

3                      10                   D              4

3                        8                   A              4

3                        8                   C              4

 

Everything is working fine except the total products quantity which is wrong.

I think my GROUP BY is wrong but don't know how to make it work.

Any help would be appreciated.

 

Thx

Jeff

 

SELECT o.`id_order` AS `#`,

    o.`reference` AS `Order Number`,

    o.`total_products_wt` AS `Order Total`,

    cu.`name` AS `Currency`,    

    CONCAT(c.`firstname`, '  ', c.`lastname`) AS `Customer Name`,

    ad.`address1` AS ` Recipient Address 1`,

    ad.`address2` AS ` Recipient Address 2`,

    ad.`city` AS `City`,

    st.`name` AS `State`,

    co.`name` AS `Country`,

    ad.`postcode` AS `Postal Code`,

    cou.`iso_code` AS `Country Code`,

    c.`email` AS `Customer Email`,

    ad.`phone_mobile` AS `Customer Contact Number`,

    SUM(d.`product_quantity`) AS `Total Products`,

    o.`total_products_wt` AS `Order Total`,

    p.`reference` AS `Products SKU`,

    d.`product_quantity` AS `Product Quantity`

FROM ps_orders o

    LEFT JOIN `ps_order_detail` d ON (o.id_order = d.id_order)

    LEFT JOIN `ps_customer` c ON (o.id_customer = c.id_customer)

    LEFT JOIN `ps_address` ad ON(o.`id_customer`=ad.`id_customer`)

    LEFT JOIN `ps_state` st ON(ad.`id_state`=st.`id_state`)

    LEFT JOIN `ps_country_lang` co ON(ad.`id_country`=co.`id_country`)

    LEFT JOIN `ps_country` cou ON(ad.`id_country`=cou.`id_country`)

    LEFT JOIN `ps_product` p ON(d.`product_id`=p.`id_product`)

    LEFT JOIN `ps_currency` cu ON(o.`id_currency`=cu.`id_currency`)

WHERE o.`valid` = 1

GROUP BY o.id_order,d.id_order_detail

ORDER BY o.`id_order`