Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column
Have you ever encountered an error when attempting to query using SUM() with a condition like, “Hey, I want to know how many projects are in each project status”? You might construct the query like this:
Project::query()->select("status", DB::raw('count(status) as total'))->groupBy("status")->get();
It’s a simple and fine query. However, when you want to scope it to only this month, you might try:
Project::query()->select("status", "date", DB::raw('count(status) as total'))->whereBetween("date", [$start, $end])->groupBy("status")->get();
And then you’ll encounter an error.
Here’s a trick that will help you overcome this issue and never worry about the GROUP BY again:
You can use ANY_VALUE() with the column that you’re using in the WHERE condition:
Project::query()->select("status", DB::raw('ANY_VALUE(date)'), DB::raw('count(status) as total'))->whereBetween("date", [$start, $end])->groupBy("status")->get();
solved!