0

I am trying to write a SQL query for Magento that will show orders which were paid (non-zero total) and those which were coupon based (zero total). The Coupon-based orders should be divided by the promotion that the coupon was from. Then each row of the report show the following:

  • The promo name (Shopping cart price rule name)
  • Number of orders
  • Percentage of total orders
  • Average order total

I realize this is could be an extremely complex query, so would be happy with someone pointing out the table(s) that I will need in order to execute this. Any an all help is welcome and thanked =)

4

This seems to have done the trick

SELECT coupon_rule_name                         AS 'Promotion Used'
     , coupon_code                              AS 'Code Used'
     , COUNT(coupon_code)                       AS 'Times Used / Number of Orders'
     , SUM(subtotal)                            AS 'Cumulative Price'

     , SUM(total_paid)                          AS 'Cumulative Paid with Coupon'
     , AVG(total_paid)                          AS 'Average Order Total (W/  Coupon)'
     , AVG(subtotal)                            AS 'Average Order Total (W/O Coupon)'

     , ABS(SUM(discount_amount))                AS 'Cumulative Savings'

     , (
        SUM(discount_amount) - SUM(total_paid)
       )                                        AS 'Cumulative Loss'

     , CONCAT(ROUND((
        COUNT(coupon_code) / (SELECT COUNT(*) FROM sales_flat_order s)
       ) * 100, 1), '%')                        AS 'Percentage'

FROM     sales_flat_order
WHERE    coupon_code        IS NOT NULL
GROUP BY coupon_code
ORDER BY COUNT(coupon_code) DESC;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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