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?
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()...
Please use the below link, as the solution is already available.
Please check and you will find the solution.