"Expression #1 of SELECT list is not in GROUP BY": Two Fixes in Laravel

When grouping MySQL results, have you ever seen this error: "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column..."? In this tutorial, I will show you two ways how to handle it in Laravel.

Grouping MySQL results by a column or a combination of multiple columns is a very common task. Yet, it is not always obvious how to do it.

It especially becomes tricky when you get errors like this:

What Causes this Error?

To keep it simple - this error usually comes from an incorrect query. By that, we mean that there is a field that can't be grouped by and is unique for each row. Here's an example:

$expenses = Expense::query()
    ->select('name', 'user_id', 'date', DB::raw('sum(amount)'))
    ->groupBy(['user_id', 'date'])
    ->get();

The problem is that select() method contains more fields than groupBy() method, so we're retrieving too many columns, which causes grouping to fail.

Fix Option 1: Select the Columns

To fix this, we should define which columns we want to retrieve and to group by. In our case, we want to calculate the sum for each user on each date, so we don't need the name of the record.

The correct query:

$expenses = Expense::query()
    ->select('user_id', 'date', DB::raw('sum(amount)'))
    ->groupBy(['user_id', 'date'])
    ->get();

The result is correct:

Fix Option 2: Less Ideal - Disable Strict Mode

Another solution is a simple Laravel configuration change:

config/database.php

// ...
'mysql' => [
    'driver' => 'mysql',
    // ... other values
 
    'strict' => true,
    'strict' => false,
],
// ...

It will turn off strict mode for MySQL, which will allow you to group by any column. For example, you may include back the name column, and each grouped record will contain one of the names:

$expenses = App\Models\Expense::query()
    ->select('name', 'user_id', 'date', DB::raw('sum(amount)'))
    ->groupBy(['user_id', 'date'])
    ->orderBy('user_id')
    ->orderBy('date')
    ->get();

But it may lead to incorrect or unpredictable results.

For example, if we look at the database, we have two records for user_id=1 and date='2023-06-11'.

But our query returns ONE name with the sum(amount) row.

So while you have "solved" the issue, you may return incorrect results.