Navigate
X
    OlgaK
  • 9 years ago
    • Categories: SQL

Magento MySQL – Calculate average number of orders per day and per month

What is the average number of orders per day?

SELECT AVG( orders_num )
FROM (
SELECT created_at, COUNT( DISTINCT order_id ) orders_num
FROM `sales_flat_order_item`
GROUP BY CAST( created_at AS DATE )
)orders_per_day

What is the average number of orders per month?

SELECT AVG( orders_num ) 
FROM (
SELECT created_at, COUNT( DISTINCT order_id ) orders_num
FROM  `sales_flat_order_item` 
GROUP BY MONTH( created_at )
)orders_per_month

View on Github

Tags: average number of ordersmagentoorders per dayorders per monthqueryquery MySQLsqlSQL for Magento
OlgaK:

View Comments (1)

  • Thanks for the queries.

    You should limit the date ranges for the average orders per month. If data is over multiple years, the GROUP BY MONTH adds monthly data together from other years - ie December 2015 and December 2016 monthly figure would be added together. Group by the YEAR, that will fix the problem or add a date range.

    SELECT AVG( orders_num )
    FROM (
    SELECT created_at, COUNT( DISTINCT order_id ) orders_num
    FROM `sales_flat_order_item`
    GROUP BY MONTH( created_at ), YEAR(created_at)
    )orders_per_month

Leave a Comment