Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Magento - Report for count and revenue of promotions
SELECT
sr.`name` as rule_name,
count(*) as total_orders,
SUM(sfo.`total_paid`) as revenue,
sr.`rule_id`,
sfo.`applied_rule_ids`
FROM
`sales_flat_order` sfo,
`salesrule` sr
WHERE
sfo.`status` NOT LIKE 'closed'
AND
sfo.`created_at` > '2012-01-01'
AND
sfo.`created_at` < '2013-12-31'
AND
sfo.`applied_rule_ids` != ''
AND
sr.`rule_id` IN (sfo.`applied_rule_ids`)
GROUP BY
sr.`rule_id`
ORDER BY rule_name DESC
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment