I have a problem in group by in Magento 1.9 , I have tested all the query , it works except the last line

SELECT DISTINCT catalog_product_entity_text.value   AS nomproduit,
            sales_flat_order.created_at         AS datedecommande,
            sales_flat_order.subtotal           AS prixavantremise,
            sales_flat_order.grand_total        AS prixapresremise,
            sales_flat_order_address.firstname  AS prenom,
            sales_flat_order_address.lastname   As nom,
            sales_flat_order_address.telephone  AS telephone,
            sales_flat_order_address.country_id AS pays
FROM catalog_product_entity t,
     LEFT JOIN sales_flat_order AS sales
               ON (sales.entity_id = catalog_product_entity_text.entity_id)
     LEFT JOIN sales_flat_order_address AS address
               ON (address.entity_id = catalog_product_entity_text.entity_id)
     LEFT JOIN catalog_product_entity as sku
               on (sku.entity_id = sales.entity_id)
where sales_flat_order.coupon_code like 'xxxxx';/*
group by  t.sku,sales_flat_order.entity_id;*/

Can anyone help me , I got stuck at this problem Thanks in advance

  • Can you share full query.? the above query is being incomplete. Jan 28, 2020 at 8:50

1 Answer 1


Sorry, but this SQL statement doesnt make any sense at the moment. I am not even sure what you want to do here. Actually you have comparings of at whole different values there like

on (sku.entity_id = sales.entity_id)

the entity_id out of the catalog_product_entity table has nothing to do with the entity_id of the sales_flat_order table.

If you explain what you want to achieve with this SQL statement, i may can help you.


SELECT distinct
product.sku   AS nomproduit,
sales.created_at         AS datedecommande,
sales.subtotal           AS prixavantremise,
sales.grand_total        AS prixapresremise,
sales_address.firstname  AS prenom,
sales_address.lastname   As nom,
sales_address.telephone  AS telephone,
sales_address.country_id AS pays
sales_flat_order_item as sales_item
LEFT JOIN catalog_product_entity as product ON product.entity_id=sales_item.product_id
LEFT JOIN sales_flat_order as sales ON sales.entity_id=sales_item.order_id
LEFT JOIN sales_flat_order_address as sales_address ON sales_address.parent_id=sales.entity_id AND address_type='billing'
WHERE sales.coupon_code = 'zpa2012'
group by product.sku, sales.entity_id

maybe this is what you want ?

  • I want to get orders by their promocode and filtered by sku in order to show : orderdate,pricebefore reduction, telephone .. Jan 27, 2020 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.