How to Retrieve Total Orders Per Day in Magento 2 for the Last Month

When managing a Magento 2 store, analyzing daily order trends is crucial for tracking business performance. This guide explains how to retrieve the total number of orders per day for the last month using a MySQL query.

Step 1: Understanding the Query

The query leverages Magento’s sales_order table, which stores details about each order placed on your website. By grouping orders by their creation date, you can calculate the total number of orders for each day.

Step 2: The SQL Query

Below is the SQL query to fetch total orders per day for the last month:

SELECT 
    DATE_FORMAT(created_at, '%Y-%m-%d') AS order_date, 
    COUNT(*) AS total_orders
FROM 
    sales_order
WHERE 
    created_at >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY 
    DATE_FORMAT(created_at, '%Y-%m-%d')
ORDER BY 
    order_date DESC;

You can modify the query to compare the total orders for the last 4 days of this year and the same 4 days of the previous year. Here's how:

SELECT 
    DATE_FORMAT(created_at, '%Y-%m-%d') AS order_date, 
    COUNT(*) AS total_orders
FROM 
    sales_order
WHERE 
    (
        DATE(created_at) BETWEEN CURDATE() - INTERVAL 3 DAY AND CURDATE()
        OR 
        DATE(created_at) BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 YEAR) - INTERVAL 3 DAY AND DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
    )
GROUP BY 
    DATE_FORMAT(created_at, '%Y-%m-%d')
ORDER BY 
    order_date;

How to Retrieve Total Orders Per Day in Magento 2 for the Last Month