CODEWITHSUNDEEP
laravel
juan miguel
juan miguel

Reputation: 73

Laravel error: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT()

Anyone familiar with this Laravel Error:

"SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select office, avg(q1) as q1, avg(q2) as q2 from feedback)"

This is my controller code:

$feedback_data = DB::table('feedback')
    ->select(DB::raw('office, avg(q1) as q1, avg(q2) as q2'))
    ->get();
return view('/feedback/index')->with('feedback_data', $feedback_data);

Upvotes: 7

Views: 14611

Answers (2)

Haritsinh Gohil
Haritsinh Gohil

Reputation: 6272

While using MIN(),MAX(),COUNT() AVG() like agregate functions you have to use GROUP BY , But As per latest MYSQL version You have to use all the columns in select query as a GROUP BY.

So for solving this error there are 2 ways:

  1. You can use GROUP BY on relevant column.

  2. You can disable the strict mode in config/database.php by setting it to false.

GROUP BY is already showed in one of the answer given here so we will talk about strict mode, so laravel has strict mode as true by default which does not allow to group by on single column so you have to set the strict mode to false if you want group by on single column.

Disabling "strict" mode, returns to the <= MYSQL 5.6 behavior while enabling "strict" mode, set it to the MYSQL 5.7 behavior and we want to disable it because disabling it will allow us to group by on single column.

So for setting strict mode go to config/database.php and set strict mode to false.

'mysql' => [
        'driver' => 'mysql',
        'url' => env('DATABASE_URL'),
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => false, //it will be true by default set this to false
        'engine' => null,
        'options' => extension_loaded('pdo_mysql') ? array_filter([
            PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        ]) : [],
    ],

Upvotes: 11

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

You have to use groupBy():

$feedback_data = DB::table('feedback')
    ->select(DB::raw('office, avg(q1) as q1, avg(q2) as q2'))
    ->groupBy('office')
    ->get();

Upvotes: 5

Related Questions