Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
Hello,
I have a simple query :
$query = 'SELECT count(date) AS total, substr(date, 1, 4) AS annee FROM sort_orders_by_times WHERE date LIKE \'' . $date .'%\'';
return DB::select($query);
This runs fine in the terminal mariadb client, however laravel gives me this 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
I tried to disable strict mode, which doesn't sound like a good solution anyway, i tried DB::raw and the pdo driver, nothing worked. What am i doing wrong ? Thank you !
@laurent1979 Well, your query doesn't make much sense. How is it supposed to count something that isn't aggregated? You just need to do what the error suggests:
$query = 'SELECT count(date) AS total, substr(date, 1, 4) AS annee FROM sort_orders_by_times WHERE date LIKE \'' . $date .'%\' group by annee';
return DB::select($query);
Thank you for your reply,
I did that and it worked, I wanted to know why the query I had worked perfectly when I typed it in the terminal, but not with laravel. I mean in the terminal or a SQL GUI, this query works fine
@laurent1979 To my understanding, disabling strict mode as you mentioned, should make it behave like earlier versions where it was possible to make such a query with nonaggregates. Make sure you clear your config cache.
Either way, grouping should be the way you want to go about it anyway. Most engines now behave like this, so avoiding it would be considered bad practice.
I got it thank you !
Please or to participate in this conversation.