Laravel – multiple select query

May 6, 2022

I want to pull data from two columns in my database table, I’m not very good at querying SQL. When I do this query I get this error:

Expression #2 of SELECT list is not in GROUP BY clause and contains…

What am I missing?

My query:

$top_orders = OrderItem::select('name','productId', DB::raw('count(name) as total'))->groupBy('name')->orderBy('total','desc')->take(5)->get();

>Solution :

It is SQL Standard feature and its not anything to do with laravel. You can add required column to group by or use an aggragete function like sum etc.

If you specify the GROUP BY clause, columns referenced must be all the columns in the SELECT clause that do not contain an aggregate function. These columns can either be the column, an expression, or the ordinal number in the column list.

$top_orders = OrderItem::select('name','productId', DB::raw('count(name) as total'))
  ->groupBy('name','productId')
  ->orderBy('total','desc')
  ->take(5)
  ->get();