Magento group by clause on date field

Magento group by clause on date field

September 23, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento group by clause on date field

Magento: Group By Clause on Date Field

Are you facing issues while trying to fetch the total number of orders along with SUM, MIN, MAX, and AVG of ‘grand_total’ for specified dates in Magento? You’re not alone! Let’s dive into a common query challenge that many developers encounter.

The Current Approach

The developer provided an initial query using Magento’s collection methods:

$collection->getSelect()
            ->columns( 'SUM(base_grand_total) AS total' )
            ->columns( 'COUNT(*) AS orders_count' )
            ->columns( 'DATE_FORMAT(created_at, "%d") AS order_day' )
            ->columns( 'DATE_FORMAT(created_at, "%d/%m/%y") AS order_date' )
            ->columns( 'AVG(base_grand_total) AS avg_total' )
            ->columns( 'MAX(base_grand_total) AS max_total' )
            ->columns( 'MIN(base_grand_total) AS min_total' )
            ->where( 'DATE_FORMAT(created_at, "%m") = ?', $month )
            ->where( 'DATE_FORMAT(created_at, "%Y") = ?', $year )
            ->group( 'DATE_FORMAT(created_at, "%d-%m-%y")' );

The Issue at Hand

The developer noticed a discrepancy between the query results and the built-in Sales > Order grid in Magento. While the custom grid showed 3 orders on the 26th of the specified month, Magento’s default grid displayed only one order for that date.

Upon investigation, the developer discovered that the created_at field is stored as a timestamp (e.g., “2014-09-04 02:50:04”) in the database. However, when formatted, it appears as “Sep 3, 2014 4:50:04 PM.”

The Root Cause

The primary issue lies within the groupBy clause of the query. When grouping by a date field, Magento expects the date to be in a consistent format across all rows. In the provided example, the developer is using the DATE_FORMAT function twice in the WHERE clause with different formats.

To resolve this issue, you should apply a consistent date format when grouping by the created_at field. Here’s an updated version of the query:

$collection->getSelect()
            ->columns( 'SUM(base_grand_total) AS total' )
            ->columns( 'COUNT(*) AS orders_count' )
            ->where('MONTH(created_at)', $month)
            ->where('YEAR(created_at)', $year)
            ->group( 'DATE_FORMAT(created_at, "%m-%d-%y")' );

Key Takeaways

  1. Ensure consistency in date formatting when grouping by a date field in Magento.
  2. Avoid using multiple DATE_FORMAT functions with different formats within the same query.
  3. Utilize built-in MySQL functions like MONTH() and YEAR() for better performance.

By following these best practices, you should be able to fetch accurate results from your custom queries without discrepancies between your custom grid and Magento’s default Sales > Order grid.

Happy coding! Don’t forget to explore more Magento development insights on LaravelCompany.com.

Database Admin Orders Errors

Our network