Skip to content

Instantly share code, notes, and snippets.

@mehh
Created January 28, 2019 23:37
Show Gist options
  • Save mehh/a32e8895c848dc0f06f106f9bca61bb9 to your computer and use it in GitHub Desktop.
Save mehh/a32e8895c848dc0f06f106f9bca61bb9 to your computer and use it in GitHub Desktop.
Magento1 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);
//
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment