#magento get average #order, discount, item count, and order amount #report by month and year with direct #SQL

main.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