Skip to content

Instantly share code, notes, and snippets.

@Maksold
Last active Oct 25, 2018
Embed
What would you like to do?
#magento get average #order, discount, item count, and order amount #report 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, sub_query.year_ordered
ORDER BY year_ordered DESC,
month_ordered DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment