Displays promotion name, how many orders promo was applied to, percentage of those orders and the average total of those orders.

promotions.sql

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;

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 |
+----------------------------+-------------+-------------+------------+