Why group by is not working properly? laravel

user_10218061 0
Viewed 49

I'm currently using groupBy to group concatenated (book name and book_author). I'm intentionally using groupBy because I have other columns to get their average, and sum.

I initially used this code below. But it returning me an error.

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated

Reference::select('*')
    ->selectRaw("CONCAT(book_name, ' ', book_author) AS book")
    ->groupBy('type');

If I add an id column inside groupBy, it does not returning an error. But it does not order, I still getting duplicate book.

Reference::select('*')
    ->selectRaw("CONCAT(book_name, ' ', book_author) AS book")
    ->groupBy(['id', 'type']);

Someone, how to achieve this properly?

laravel php
2 Answers

TLDR; When you use group by in strict mode, your selected fields must be aggregated fields.

Let's say you have two books with the same type.

book_name book_name type
First author 1
Second editor 1

When you group by type, those having the same type will be merged into one. So wich result will it be for CONCAT(book_name, ' ', book_author) ?

  • First author ?
  • Second editor ?
  • First editor ?
  • Second author?

The query doesnt gamble when strict mode is active, you need to aggregate the fields, for example LISTAGG(book_name , ',') is an aggregationg of the field book_name and result in 'first,second'.

If you disable the strict mode ('strict' => false,) in the file config/database.php, the result might be any of the 4 listed above as it is kinda random.

Other example for aggregation functions: SUM(), AVG(), COUNT()...

user_43699190

Please use the below link, as the solution is already available.

Solution Link.

Please check and you will find the solution.

user_42141750
Related