Magento1 Cart Price Rule SQL

magento_cart_price_rule.sql

// https://magento.stackexchange.com/questions/40649/magento-direct-sql-query-for-coupon-based-orders
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;









//  Magento get average order, discount, item count, and order amount break down by month and year with direct SQL
SELECT sub_query.month_ordered,
       sub_query.year_ordered,
       AVG(sub_query.base_subtotal) AS average_base_subtotal,
       AVG(sub_query.discount_amount) AS average_discount_amt,
       AVG(sub_query.order_qty) AS average_total_item_count,
       COUNT(sub_query.entity_id) AS total_orders
FROM
  (SELECT so.entity_id,
          MONTH(so.created_at) AS month_ordered,
          YEAR(so.created_at) AS year_ordered,
          so.base_subtotal,
          so.discount_amount,
          so.total_item_count AS order_qty,
          so.increment_id
   FROM `sales_flat_order` AS so
   INNER JOIN `sales_flat_order_item` AS si ON si.order_id=so.entity_id
   GROUP BY entity_id) AS sub_query
GROUP BY sub_query.month_ordered
ORDER BY year_ordered DESC,
         month_ordered DESC
         
         
         
         
         
         
         
         
         
// Get the each customer sales and orders total

SELECT
    -- Round to two decimal places and prepend with $
    CONCAT('$', FORMAT(SUM(sales_flat_order.`grand_total`), 2)) AS 'Lifetime Sales',
    COUNT(sales_flat_order.entity_id) AS 'Orders',
    customer_entity.email AS 'Email',
    MAX(sales_flat_order.created_at) AS 'Most Recent Order Date'
FROM `customer_entity`
    LEFT JOIN sales_flat_order ON customer_entity.entity_id = sales_flat_order.customer_id
GROUP BY customer_entity.entity_id
ORDER BY SUM(sales_flat_order.`grand_total`) DESC
LIMIT 500;

# Get Orders Status

SELECT
    sales_order_status.label AS 'Status',
    COUNT(sales_flat_order.entity_id) AS 'Orders'
FROM sales_flat_order
    LEFT JOIN sales_order_status ON sales_flat_order.status = sales_order_status.status
GROUP BY sales_flat_order.status
ORDER BY COUNT(sales_flat_order.entity_id) DESC;

# Get the each month Order Status

SELECT
    -- "Year - Month"
    CONCAT(YEAR(sales_flat_order.created_at), ' - ', MONTHNAME(sales_flat_order.created_at)) AS 'Month',
    SUM(IF(`status` = 'canceled', 1, 0)) AS 'Canceled',
    SUM(IF(`status` = 'closed', 1, 0)) AS 'Closed',
    SUM(IF(`status` = 'complete', 1, 0)) AS 'Complete',
    -- Custom status
    SUM(IF(`status` = 'complete_partially_shipped', 1, 0)) AS 'Partially Shipped',
    SUM(IF(`status` = 'processing', 1, 0)) AS 'Processing',
    -- Custom status
    SUM(IF(`status` = 'shipped', 1, 0)) AS 'Shipped'
FROM sales_flat_order
GROUP BY MONTH(sales_flat_order.created_at);

           
           
           
           
           
           
           
           
           
//