0

Title says it. I am looking for a raw query (direct sql) to tell me a promotions name, and how many orders it has been used on. Results should look something like this

+----------------------------+-------------+
| Promotion Name             | # of Orders |
+----------------------------+-------------+
| Full Price                 |           2 |
| Looting Sale               |           3 |
+----------------------------+-------------+

I am having a hard time even finding where the promotion name is kept, much less the times of orders

1

This view works. It displays promotion name, how many orders promo was applied to, percentage of those orders and the average total of those orders.

CREATE VIEW promotions  AS
SELECT 'Full Price'     AS 'Promotion Name'
     , COUNT(entity_id) AS '# of Orders' 
     , CONCAT(ROUND(
        (((SELECT COUNT(b.entity_id) FROM sales_flat_order b WHERE b.applied_rule_ids IS NULL) 
        / (SELECT COUNT(entity_id) FROM sales_flat_order a)) * 100), 1), '%') AS '% of Orders'
     , ROUND(AVG(subtotal), 2) AS 'Avg. Total'

FROM sales_flat_order

WHERE applied_rule_ids IS NULL

UNION ALL

SELECT sr.name AS 'Promotion Name'
     , (SELECT COUNT(entity_id) FROM sales_flat_order WHERE applied_rule_ids = sr.rule_id) '# of Orders'
     , CONCAT(ROUND(
        (((SELECT COUNT(b.entity_id) FROM sales_flat_order b WHERE b.applied_rule_ids = sr.rule_id) 
        / (SELECT COUNT(entity_id) FROM sales_flat_order a)) * 100), 1), '%') AS '% of Orders'
     , ROUND(AVG(sfo.subtotal), 2) AS 'Avg. Total'

FROM salesrule        sr
   , salesrule_coupon src
   , sales_flat_order sfo

WHERE sr.rule_id = src.rule_id AND sr.rule_id = sfo.applied_rule_ids

GROUP BY sr.rule_id;

And then just

SELECT * FROM promotions;

Output:

+----------------------------+-------------+-------------+------------+
| Promotion Name             | # of Orders | % of Orders | Avg. Total |
+----------------------------+-------------+-------------+------------+
| Full Price                 |          25 | 71.4%       |     308.72 |
| $201 CREDIT - AMAZON LOCAL |           7 | 20.0%       |     385.71 |
| Looting Sale               |           3 | 8.6%        |     277.67 |
+----------------------------+-------------+-------------+------------+

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.