Jump to content

SQL Manager : request to export orders


Recommended Posts



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.





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`
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...