Maybe your issue is due to the fact that you are using a MySQL server vith version 5.7.5+. From this version on the way GROUP BY works is changed since they make it behave in order to be SQL99 compliant (where in previous versions it was not).

Try to do a full group by or change the configuration of your MySQL server.

Link to official MySQL doc where full GROUP BY is explanined

More safe method instead of disabling strict ('strict' => false) what you could do is pass an array to the config, enabling only the modes that you want:

// config/database.php
    
    'connections' => [
        //...
        'mysql' => [
            //...
            'strict'      => true,
            'modes'       => [
                //'ONLY_FULL_GROUP_BY', // Disable this to allow grouping by one column
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                //'NO_AUTO_CREATE_USER', // This has been deprecated and will throw an error in mysql v8
                'NO_ENGINE_SUBSTITUTION',
            ],
        ],
    ],

For anybody who is still getting the same error after changing that setting, try clearing the config cache by running php artisan config:cache

There are ways to fix this

#1

Get only the columns we are grouping by, in this case category_id.

NOTE: Columns in select must be present in groupBy, and vice versa.

$posts = Post::query()
    ->select('category_id')
    ->groupBy('category_id')
    ->get();

#2

But I want all columns!

Okay, so you want to get all columns. Then the trick is to simply not use groupBy() on a database level. Instead, you can use it with the returned collection instead.

$posts = Post::query()
    ->get()
    ->groupBy('category_id');

[
 
'1' => [
    ['id' => 1, 'name' => 'Post 1', 'category_id' => 1, 'author_id' => 4 'visits' => 32],
    ['id' => 2, 'name' => 'Post 2', 'category_id' => 1, 'author_id' => 8 'visits' => 12],
],
'2' => [
    ['id' => 3, 'name' => 'Post 3', 'category_id' => 2, 'author_id' => 12 'visits' => 201],
    ['id' => 4, 'name' => 'Post 4', 'category_id' => 2, 'author_id' => 4 'visits' => 0],
],

]

#3

It is possible to simply disable "strict mode" in Laravel, by setting it to false in the database.php config file. While possible I cannot recommend doing so. It is better to spend the time learning how to write proper SQL queries, as the results given by turning "strict mode" off, can be unpredictable and lead to problems down the road.

Reference

https://sinnbeck.dev/posts/laravel-groupby-error

You can define this line before your query, let's suppose you want to use groupBy so for that instead of changing the config strict to false, simply add this line before where you had used groupBy:

\DB::statement("SET SQL_MODE=''");//this is the trick use it just before your query where you have used group by. Note: make sure your query is correct.

//this is just an example code.

$Rspatients = DB::table('reports')
->select(
    DB::raw("day(created_at) as day"),
    DB::raw("Count(*) as total_patients"))

->orderBy("created_at")
->groupBy(DB::raw("day(created_at)"))
->get();

Go to config/database.php

Update strict value false.

return [
   'connections' => [
      'mysql' => [
         'strict' => false
       ]
   ]
]

My company uses raw SQL to run group by without risking changing mysql settings.

here is an working example :

public static function getPositivesDaily($start_date, $end_date, $admin_id)
{
    $positives = DB::select(
        'select COUNT(inspections.id) as total,DATE_FORMAT(inspections.created_at, :format) as date
            from inspections
            where inspections.created_at between :start_date and :end_date
            and inspection_results = 1
            and admin_id = :admin_id
            GROUP BY date',
    ['format'=>'%Y-%m-%d', 'start_date'=>$start_date, 'end_date'=> $end_date, 'admin_id'=>$admin_id]
    );


    return $positives;
}

Ask me anything about this code if you don't understand and I will reply as soon as I can. cheers.